By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,426 IT Pros & Developers. It's quick & easy.

to check from table1 column if the row has value it will update to table2 else it wi

P: 1
Expand|Select|Wrap|Line Numbers
  1. table1---- Id name adviceno chequeno
  2.             11 abc 1         null
  3.             22 bcx 2         null
  4.             31 tex null      A2093
  5.             54 yrt 3         null
  6.             52 ths null      A3744
  7.  
  8. table2--- Id name  alldetails_cheque
  9.           11 abc     null  
  10.           22 bcx     null  
  11.           31 tex     null   
  12.           54 yrt     null   
  13.           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:

Expand|Select|Wrap|Line Numbers
  1. table2---- Id name  alldetails_cheque (after update)
  2.             11 abc    1
  3.             22 bcx    2
  4.             31 tex   A2093
  5.             54 yrt    3
  6.             52 ths   A3744
SQL:
1.
Expand|Select|Wrap|Line Numbers
  1. update table2 
  2. set t2.alldetails_cheque=t1.adviceno 
  3. from table1 as t1 and table2 as t2
  4. on t1.id=t2.id and t1.name=t2.name 
  5. where chequeno is null
2.
Expand|Select|Wrap|Line Numbers
  1. update table2 
  2. set t2.alldetails_cheque=t1.chequeno 
  3. from table1 as t1 and table2 as t2
  4. on t1.id=t2.id and t1.name=t2.name 
  5. 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
  1. protected void Button7_Click1(object sender, EventArgs e)
  2. {
  3.  
  4. DataTable dt = new DataTable();
  5. string query = "select * from table1";
  6. using (SqlConnection con = new SqlConnection(con_str))
  7. {
  8. using (SqlCommand cmd = new SqlCommand(query))
  9. {
  10. using (SqlDataAdapter da = new SqlDataAdapter())
  11. {
  12. cmd.CommandType = CommandType.Text;
  13. cmd.Connection = con;
  14. da.SelectCommand = cmd;
  15. da.Fill(dt);
  16. }
  17. }
  18. }
  19.  
  20. try
  21. {
  22.  
  23. string query2;
  24. // string query3;
  25. using (SqlConnection con = new SqlConnection(con_str))
  26. {
  27. con.Open();
  28.  
  29. foreach (DataRow row in dt.Rows)
  30. {
  31. string str = row["AdviceNo"].ToString();
  32.  
  33.  
  34. //The conditions 
  35.  
  36. if (str.Equals(null))
  37. {
  38.  
  39.  
  40.  
  41. query2 = "update table2 set t2.alldetails_cheque=t1.adviceno from table1 as t1 and table2 as t2
  42. on t1.id=t2.id and t1.name=t2.name where chequeno is null";
  43. SqlCommand cmd2 = new SqlCommand(query2, con);
  44. cmd2.ExecuteNonQuery();
  45. }
  46. else 
  47. {
  48. query2 = "update table2 set t2.alldetails_cheque=t1.adviceno from table1 as t1 and table2 as t2
  49. on t1.id=t2.id and t1.name=t2.name where chequeno is null ";
  50. SqlCommand cmd2 = new SqlCommand(query2, con);
  51. cmd2.ExecuteNonQuery();
  52.  
  53.  
  54. }
  55.  
  56. }
  57.  
  58. con.Close();
  59. }
  60.  
  61. }
  62. catch (Exception ex)
  63. {
  64. Response.Write(ex.Message);
  65.  
  66. }
  67. finally
  68. {
  69.  
  70. Label2.Text = "updated";
  71. }

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

i want to update alldetails_cheque from chequeno
Sep 9 '14 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.