Shulou(Shulou.com)06/01 Report--

Update the A1 sheet A2 column of Table A with the data of Table B (B1 column, B2)

SQL Server:

Update A SET A.A1 = b.B1. A.A2 FROM A, B WHERE A.ID1 = B.ID1 and A.ID2 = B.ID2

Access:

Update A, B set A.A1 = B.B1 where A.ID1 = B.ID1 and A.ID2 = B.ID2After-or update An INNER JOIN B ON A.ID1 = B.ID1 AND A.ID2 = B.ID2 SET A.A1 = B.B1

Update more than one piece of data at a time through a SQL statement. SQL statements are written slightly differently, as follows:

Method 1:

Update T1set dc= (select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1) WHERE T1.AID in (SELECT AID FROM T1 INNER JOIN T2 ON t1.A=T2.A1 AND T1.B=T2.B1)

Note: if I mark my red in, it will make an error if I replace 'in'' with'=', but it can be replaced by'='in the following B method, which can be written in C method.

Method 2:

Update T1set dc= (select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1) WHERE T1.AID in (SELECT T1.AID FROM T2 WHERE t1.A=T2.A1 AND T1.B=T2.B1)

Method 3:

Update T1set dc= (select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1) WHERE T1.AID = (SELECT T1.AID FROM T2 WHERE t1.A=T2.A1 AND T1.B=T2.B1)

Method 4:

Update T1set dc= (select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1) WHERE T1.A = (SELECT T1.A FROM T2 WHERE T1.A=T2.A1 AND T1.B=T2.B1)

Method 5:

Update T1set dc= (select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1) WHERE T1.A = (SELECT T2.A1 FROM T2 WHERE T1.A=T2.A1 AND T1.B=T2.B1)

Method 6:

Update T1set dc= (select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1) WHERE T1.An in (SELECT T2.A1 FROM T2 WHERE T1.A=T2.A1 AND T1.B=T2.B1)

Note: if the above method is rewritten as follows, it will make an error.

Update T1 set dc= (select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1) WHERE T1.An in (SELECT T1.A FROM T1 INNER JOIN T2 ON t1.A=T2.A1 AND T1.B=T2.B1)

This method will update all data that meets the condition T1.An in (), so it is incorrect and cannot be written in this way.

Method 7:

Update T1set dc= (select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1 AND T1.DC=T2.DC1) FROM T1, T2WHERE T1.A=T2.A1 AND T1.B=T2.B1

Method 8:

Update T1 set T1.dc=T2.dc1FROM T1INNER JOIN T2 ON T1.A=T2.A1 AND T1.B=T2.B1WHERE t1.A=T2.A1 AND T1.B=T2.B1

Method 9:

Update T1set T1.dc=T2.dc1FROM T1, T2WHERE T1.A=T2.A1 AND T1.B=T2.B1

Please pay special attention to the code on the red mark in the above method.

General section: although it is a simple update name, there are many ways to write it, and each has its own advantages and disadvantages.

Do you have a simpler way to write or something easier to understand? If there is, please bring it up for discussion, thank you!

Explain the test environment: the above code is feasible to be verified in MSSQL2005.

Note: the part I marked with gray here can be removed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.