Expand|Select|Wrap|Line Numbers
- 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
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:
Expand|Select|Wrap|Line Numbers
- table2---- Id name alldetails_cheque (after update)
- 11 abc 1
- 22 bcx 2
- 31 tex A2093
- 54 yrt 3
- 52 ths A3744
1.
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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.adviceno from table1 as t1 and table2 as t2
- on t1.id=t2.id and t1.name=t2.name where chequeno 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 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