Oracle update set select from associated updates

There is a need in the work, now there are some data in the new table is the same as the basic cousin, so that only need to move the data in the cousin to the new table can be, while the different fields to modify the data can be modified in the field, it is necessary to specify a condition, such as the primary key ID, to modify a record, so that , is there a way of bulk operation?

Sql>select *From Wwn2; Town ID------------------------------222 222111 111ww' JJ 111llll 1111dddd2222Lllldf111Lllldf111DSAFDF1113435 111LJJJJJ222DSAFDF1113435 111LJJJJJ222SQL> select *From WWM5; Town ID-------------------- ----------Lllldf111Test9984SQL> select wwm2.* from WWM2,WWM5 where wwm2.id=Wwm5.idtown ID------------------------------111 111ww' JJ 111lllldf 111Lllldf111DSAFDF1113435 111DSAFDF1113435 1118rows selected.--need to update 8 data is correct the following is an incorrect practice: SQL> Update wwm2 Set wwm2.town= (select Wwm5.town from WWM5 where wwm5.id=wwm2.id)13rows updated. SQL> select *From wwm2; Town ID------------------------------222Lllldf111Lllldf111 1111 2222Lllldf111Lllldf111Lllldf111Lllldf111 222Lllldf111Lllldf111 22213rows selected.--you can see that 13 records have been updated, the update is correct, and the non-compliant update is NULL. The following is the correct method to resolve: Method One: SQL>Update wwm22 Set town= (select Town from Wwm5 where wwm5.id=wwm2.id)3 Where id= (select Wwm5.id from WWM5 where wwm5.id=wwm2.id) method two: SQL>Update wwm2 set town= (select town from Wwm5 where wwm5.id=wwm2.id) where exists (select1 from WWM5 where wwm5.id=wwm2.id) method Three:1Declare2cursor CUR_WWM is a select Town,id from WWM5; 3begin4 formy_wwm in CUR_WWM loop5 Update wwm2 Set town=My_wwm.town6 where id=my_wwm.id; 7end Loop; 8End; Description: If the SELECT clause can return multiple rows of records, but the record that is appropriate for the Where condition is unique, the SELECT clause that returns a single row returns multiple rows of errors, because update can only follow the WHERE clause (inner where) A matching update of the corresponding record can only be one at a time.

The actual SQL used is:

Update table1 s Set s.yesterday = (select yesterday from Table1_back sb where sb.tagname = s.tagname and rownum = 1) where S.tagname = (select TagName from Table1_back sb where sb.tagname = S.tagname);
Update table1 s Set s.yesterday = (select yesterday from Table1_back sb where sb.tagname = s.tagname and rownum = 1) where Exists (select 1 from table1_back sb where sb.tagname = S.tagname);

Reference source link : 52589181

Blog is to remember that they are easy to forget things, but also a summary of their work, the article can be reproduced, without copyright. Hope to do their own efforts to do better, we work together to improve!

If there is any problem, welcome to discuss together, code if there is a problem, you are welcome to the great God!

Oracle update set select from associated updates