About the update set from where

About Update Set fromwhere

Here's an example:

Two Tables A, B, to make the Memo field value in B equal to the name value of the corresponding ID in table a   
  table A:id,name   
        & nbsp   1       Wang   ,
            2       Li  & nbsp
            3       Zhang   
  Table b:id,clientname   &NBSP
            1         
            2   
            3    (MS SQL Server) statement: update    b   set    ClientName    =   a.name    from    a,b   where   a.id   =   b.id  

(ORALCE) Statement:update b set (ClientName) = (SELECT name from a WHERE b.id = a.id)

(Mysql) Statement:: UPDATE A, B SET A1 = B1, A2 = B2, A3 = B3 WHERE a.id = b.id

Update Set from statement format

When both where and set need to correlate a table for querying, the entire update executes, requiring two scans of the associated table, which is obviously less efficient.
For this scenario, the workaround for Sybase and SQL Server is to use the UPDATE... SET... from ... WHERE ... Syntax, which is actually getting updated data from the source table.

In SQL, table joins (left joins, right joins, Inner joins, and so on) are often used in SELECT statements, in the case of SQL syntax, which can also be used for update and DELETE statements, using J in these statements Oin also often get a multiplier effect.

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!

Syntax supported by both ORALCE and DB2:

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 wording is:
UPDATE A SET A1 = B1, A2 = B2, A3 = B3 from a left JOIN B on a.id = b.ID

Personal feeling MS SQL Server's Update syntax is more powerful. MS SQL Server notation:
UPDATE A SET A1 = B1, A2 = B2, A3 = B3 from A, B WHERE a.id = b.id

The syntax for Oracle and DB2 is more cumbersome, as follows:

UPDATEA SET(A1, A2, A3) = (SELECT B1, B2, B3 fromB WHERE a.id = b.id)
WHERE ID in (SELECT b.id fromB WHERE a.id = b.id)

The syntax for MySQL is:

UPDATE A, B SET A1 = B1, A2 = B2, A3 = B3 WHERE a.id = b.id

About the update set from where