Member Avatar for kaziraja

can u please help me with this code in charp
in need to update bulk data .

if
t1.cola=t2.cola
t1.colb=t2.colb from tab1 t1 and tab2 t2
where t2. colc is not null
then,
set t1.colc=t2.colc

else
set t1.colc=t2.cold where t2.colc is null

  • 2 Contributors
  • 5 Replies
  • 119 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by kaziraja

Dani AI

Generated

As observed, this is really an SQL problem rather than C# logic. The simplest, safest fix is a single set-based UPDATE that picks the non-null value from table1 (adviceno preferred, otherwise chequeno) and writes it into table2. Doing a per-row loop in C# that fires global UPDATE statements is fragile: each execution can touch many rows, waste resources, and overwrite prior results.

A concise SQL approach (pick one) — COALESCE or CASE — and cast if types differ:

UPDATE t2
SET alldetails_cheque = COALESCE(CAST(t1.adviceno AS varchar(50)), t1.chequeno)
FROM dbo.table2 AS t2
JOIN dbo.table1 AS t1
  ON t1.id = t2.id
  AND t1.name = t2.name;

or

UPDATE t2
SET alldetails_cheque = CASE WHEN t1.adviceno IS NOT NULL
                             THEN CAST(t1.adviceno AS varchar(50))
                             ELSE t1.chequeno END
FROM dbo.table2 AS t2
JOIN dbo.table1 AS t1 ON t1.id = t2.id;

Practical notes and troubleshooting:

  • Ensure destination column can hold both formats (make it varchar) or explicitly CAST types.
  • If id is a unique key, join only on id to avoid accidental duplicates.
  • Preview with a SELECT using the same JOIN and COALESCE/CASE before running the UPDATE.
  • Avoid checking DataRow values with ToString(); use row.IsNull("AdviceNo") or row.Field<int?>("AdviceNo") when reading DBNulls. That prevents incorrect branching in client code.

To run from ASP.NET, execute the single UPDATE with one SqlCommand/ExecuteNonQuery inside a using block (optionally inside a transaction). Back up data or wrap in a transaction while testing. This set-based method is faster, simpler, and less error-prone than looping and issuing repeated UPDATEs.

Recommended Answers

All 5 Replies

Member Avatar for AleMonteiro

This is not CSharp, it's SQL.
Try using the SQL Forum, and also provide a little more information on what you are trying to do.

Edited by AleMonteiro
Member Avatar for kaziraja

yes, You are this is SQL.

okay i am giving you details abt it.

table1---- Id name adviceno chequeno
11 abc 1 null
22 bcx 2 null
31 tex null A2093
54 yrt 3 null
52 ths null A3744

table2--- Id name adviceno chequeno
11 abc null null
22 bcx null null
31 tex null null
54 yrt null null
52 ths null null

i need to update from table1 to table2
table1.Id=table2.Id
Table1.name=table2.name
if advice is not null then
update advice number
else addice number will be null and chequeno will update

can you help me with this.

i need to do this in Asp.net(CSharp)

Member Avatar for kaziraja

please have a look this below ..

you will come to know abt all the details

table1---- Id name adviceno chequeno
11 abc 1 null
22 bcx 2 null
31 tex null A2093
54 yrt 3 null
52 ths null A3744

table2--- Id name alldetails_cheque
11 abc null
22 bcx null
31 tex null
54 yrt null
52 ths null

i need to update from table1 to table2
table1.Id=table2.Id
Table1.name=table2.name
if advice is not null then
update advice number
else addice number will be null and chequeno will update

output:

table2---- Id name alldetails_cheque (after update)
11 abc 1
22 bcx 2
31 tex A2093
54 yrt 3
52 ths A3744

SQL:
1. update table2 set t2.alldetails_cheque=t1.adviceno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where chequeno is null

  1. update table2 set t2.alldetails_cheque=t1.chequeno from table1 as t1 and table2 as t2
    on t1.id=t2.id and t1.name=t2.name where adviceno is null

when this query is used then both is updating..

can you help me with this.

i need to do this in Asp.net(CSharp). if adviceno is has value it will update in table2 or chequeno will update in table2 alldetails_cheque
using asp.net

Member Avatar for kaziraja 
protected void Button7_Click1(object sender, EventArgs e)
{

DataTable dt = new DataTable();
string query = "select * from table1";
using (SqlConnection con = new SqlConnection(con_str))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
}
}
}

try
{

string query2;
// string query3;
using (SqlConnection con = new SqlConnection(con_str))
{
con.Open();

foreach (DataRow row in dt.Rows)
{
string str = row["AdviceNo"].ToString();


//The conditions 

if (str.Equals(null))
{



query2 = "update table2 set t2.alldetails_cheque=t1.chequeno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.adviceno is null";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();
}
else 
{
query2 = "update table2 set t2.alldetails_cheque=t1.adviceno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.chequeno is null ";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();


}

}

con.Close();
}

}
catch (Exception ex)
{
Response.Write(ex.Message);

}
finally
{

Label2.Text = "updated";
}

i have tried this code.. but it is updating only the adviceno in alldetails_cheque

i want to update alldetails_cheque from chequeno

query2 = "update table2 set t2.alldetails_cheque=t1.chequeno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.adviceno is null";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();

this is not updating...

Member Avatar for kaziraja

This code is perfectly working...

Thank You Friend for helping me a lot

protected void Button7_Click1(object sender, EventArgs e)
{

DataTable dt = new DataTable();
string query = "select * from table1";
using (SqlConnection con = new SqlConnection(con_str))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
}
}
}

try
{

string query2;
// string query3;
using (SqlConnection con = new SqlConnection(con_str))
{
con.Open();

foreach (DataRow row in dt.Rows)
{
string str = row["AdviceNo"].ToString();


//The conditions 

if (string.IsNullOrEmpty(str))
{

query2 = "update table2 set t2.alldetails_cheque=t1.chequeno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.adviceno is null";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();
}
else 
{
query2 = "update table2 set t2.alldetails_cheque=t1.adviceno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.chequeno is null ";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();


}

}

con.Close();
}

}
catch (Exception ex)
{
Response.Write(ex.Message);

}
finally
{

Label2.Text = "updated";
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.