SQL in Update ... Select ... To update data after the query is associated with the

The most common update syntax is: Update <table_name> SET <column_name1> = <value>,set <column_name2> = <value& Gt If the value of my update is from a SELECT statement, and there are many columns, it is very troublesome to use this syntax first, to select out on the temporary variable, there are a number of OH second, then the variable is assigned 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 insert into table1 (c1, C2, C3) (select V1, v2, v3 from table2) The answer is yes, the exact syntax is as follows: UPDATE <table_name> <alias& Gt SET(<column_name>,<column_name>)=(SELECT(<column_name>, <column_name>)From <table_name> WHERE <alias.column_name> = <alias.column_name>)WHERE <column_name> <condition> <value>;

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  1 Wang   2 Lee   3   table b:id,clientname  & nbsp (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 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.

Update t_orderform SET t_orderform.sellerid =b.l_tuserid from T_orderform A left JOIN t_productinfo B on B.l_id=a.produc TID

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

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 )

Some of the last examples:

UPDATE bj_cailiaoset Mat_guige=GG. Mat_guige_titlefrom (SELECT BCL. Mat_id,bcl. Mat_guige,ma. Mat_guige_id,mgg. mat_guige_id as MAT_GUIGE_ID1,MGG. Mat_guige_title from Bj_cailiao Bclleft joins Mat_attach Ma on Ma. mat_id=BCL. Mat_idleft JOIN mat_guige mgg on MGG. mat_guige_id=Ma. mat_guige_id) Ggwhere bj_cailiao.mat_id=GG. mat_id--SELECT mat_id,mat_name,bj_cailiao.clt_id,bj_cltree.clt_id,clt_nameupdate Bj_cailiao SET BJ_CaiLiao.Mat_Name =Bj_cltree.clt_namefrom Bj_cailiao left JOIN bj_cltree on bj_cailiao.clt_id=Bj_cltree.clt_idwhere Mat_name<>clt_nameupdate distribution_matset Distribution_mat.distribution_mat_count= Distribution_mat.distribution_mat_count +temp. Plusminus_countfrom (SELECT DM.*, T.plusminus_count from distribution_mat20150407 dmright JOIN (SELECT distribution_master_id,mat_id,mpmd. PLUSMINUS_COUNT,MPMD. PLUSMINUS_TYPE,MPMM. Isnewfrom materialplusminus_master mpmmleft JOIN materialplusminus_details mpmd on MPMD. plusminus_master_id=MPMM. plusminus_master_id) T on t.distribution_master_id=DM. DISTRIBUTION_MASTER_ID and T.mat_id=DM. Mat_idwhere DM. Distribution_type=1and t.plusminus_type=1and t.isnew=1) Tempwhere Temp. distribution_mat_id=distribution_mat.distribution_mat_id

Update in SQL ... Select ... To update the data after the query is associated with the