To make the memo field value in Table B equal to the name value of the corresponding id in Table
Table a: id, name
1 Wang
2 Li
3
Table B: id, ClientName
1
2
3
(Ms SQL Server) Statement:
SQL code
Update B set ClientName = a. name from a, B where a. id = B. id
(Oralce) Statement:
SQL code
Update B set (ClientName) = (SELECT name FROM a WHERE B. id = a. id)
When both where and set need to be associated with a table for query, during the entire update execution, the associated table needs to be scanned twice, which is obviously less efficient.
In this case, the solution for Sybase and SQL SERVER is to use the UPDATE... SET... FROM... WHERE... syntax, which is actually to get the updated data FROM the source table.
In SQL, table join (left join, right join, inner join, etc.) is often used in select statements. In SQL syntax, these connections can also be used in update and delete statements, in these statements, using join results often get twice the result with half the effort.
SQL code
Update T_OrderForm SET T_OrderForm.SellerID = B. L _ TUserID
FROM T_OrderForm a left join T_ProductInfo B on B. L _ ID = A. ProductID
Used to synchronize data from two tables!
Both Oralce and DB2 support the following syntax:
SQL code
Update a set (A1, A2, A3) = (SELECT B1, B2, B3 from B where a. ID = B. ID)
Ms SQL Server does not support such a syntax, the corresponding syntax is:
SQL code
Update a set A1 = B1, A2 = B2, A3 = B3 from a left join B on a. ID = B. ID
I personally feel that the Update Syntax of ms SQL Server is more powerful. Ms SQL SERVER writing:
SQL code
Update a set A1 = B1, A2 = B2, A3 = B3 from a, B where a. ID = B. ID
Writing in Oracle and DB2 is troublesome, as follows:
SQL code
Update a set (A1, A2, A3) = (SELECT B1, B2, B3 from B where a. ID = B. ID)
Where id in (select B. id from B where a. ID = B. ID)
Author "w8700569"