SQL update select

The most common update syntax is:

UPDATE

SET =, SET =

If my updated Value is taken out of a select statement and there are many columns, It is very troublesome to use this syntax.

First, you need to select and place it on a temporary variable. There are many

Second, assign values to the variables.

It is very troublesome to add multiple columns. Can I Insert the results of the entire Select statement like Insert? As if the following

Insert into table1

(C1, c2, c3)

(Select v1, v2, v3 from table2)

The answer is yes. The specific syntax is as follows:

UPDATE

SET (,) = (

SELECT (,)

FROM

WHERE =)

WHERE;

The following is an example:

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: 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)

Update set from statement format

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.

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:

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:

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:

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:

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)