SQL Update Select

The most common update syntax is:

UPDATE

Set =, set =

This syntax is cumbersome if my update value is taken from a SELECT statement and there are many columns.

First, select it and put it on a temporary variable.

Second, the variable is then assigned.

It's a lot of trouble to have columns, can you insert the results of the entire SELECT statement like insert? It's like the bottom.

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;

Here is an example of this:

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

1 King

2 Lee

3 sheets

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 the where and set need to correlate a table for querying, the entire update executes with two scans of the associated table, which is obviously less efficient.

For this scenario, the solution for Sybase and SQL Server is to use update ... SET ... From ... WHERE ... Syntax is actually getting the 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, and in SQL syntax, these connections are also available for UPDATE and DELETE statements, and the use of joins in these statements is often to 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

Data to synchronize 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, which should be written as follows:

UPDATE A SET A1 = B1, A2 = B2, A3 = B3 from A left JOIN B on a.id = b.ID

Personally feel 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

Writing in Oracle and DB2 is more cumbersome, 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)