"Reprint" SQL update select combined statement and application

The most common update syntax is:

12	UPDATE Table_nameset column_name1 = value Whrer column_name2 = value

If my update value is taken out of a SELECT statement, and there are many columns, this syntax can be cumbersome.

First, to select out on a temporary variable, there are a lot of difficult to save. Second, assign the variable to the value.

It's a lot of trouble to make a list, can you insert the result of the entire SELECT statement like insert? Just like the following::

123	INSERT into table1 (c1, C2, C3) (SELECT v1, v2, v3 from table2)

The answer is yes, the specific syntax is as follows:

123456

UPDATE table1 Aliasset (column_name,column_name) = (SELECT (column_name, column_name) from Table2where column_name = Alias . column_name) WHERE column_name = VALUE

Here's an example: Two tables A, B, to make the Memo field value in B equal to the name Value table A for the corresponding ID in table A:

1234	ID name 1 Wang 2 Li 3 Zhang

Table B:

1234	ID ClientName 1 2 3

(MS SQL Server) statement:

1	UPDATE b SET ClientName = A.name from A, WHERE a.id = b.id

(ORALCE) Statement:

1	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 correlate a table for querying, when the entire update executes, the associated table needs to be scanned two times, 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 fact, in SQL syntax, these connections can also be used for UPDATE and DELETE statements, and the use of joins in these statements often results in a multiplier effect.

12	UPDATE t_orderform SET t_orderform.sellerid =b.l_tuseridfrom t_orderform A left JOIN t_productinfo B on B.l_id=a.prod Uctid

Used to synchronize data from two tables!

Syntax supported by both ORALCE and DB2:

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

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

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

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

"Reprint" SQL update select combined statement and application