Sql>select * from WWN2; TOWN ID------------------------------222 222 111 WW ' JJ llll 1111 dddd 2222 Lllldf Lllldf DSAFDF 3435 LJJJJJ 222 DSAFDF 3435 LJJJJJ 222 sql>
SELECT * from WWM5; TOWN ID------------------------------lllldf test 9984 sql> Select wwm2.* from WWM2,WWM5 where wwm2.id=wwm5.id TOWN ID---------------------- --------ww ' JJ Lllldf lllldf DSAFDF 3435 DSAFDF 3435 8 rows selected. --Need to update 8 piece of data is correct the following is a wrong approach: sql> update wwm2 set wwm2.town= (select Wwm5.town from WWM5 where wwm5.id=wwm2.id)
Updated.
Sql> select * from wwm2; TOWN ID------------------------------222 lllldf Lllldf 1111 2222 Lllldf
Lllldf Lllldf Lllldf 111 222 Lllldf lllldf 222 rows
Selected. --you can see that 13 records are updated, the updated criteria are correct, and those that do not meet the criteria are updated to NULL. The following is the right approach
Workaround:
method One:
sql> update wwm2
2 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 (select 1 from WW M5 where Wwm5.id=wwm2.id)
Method Three:
1 declare
2 cursor CUR_WWM is select Town,id from WWM5;
3 begin
4 for my_wwm in CUR_WWM loop
5 update wwm2 set Town=my_wwm.town
6 where id= My_wwm.id;
7 end Loop;
8 End;
Note: If a SELECT clause can return multiple rows of records, but returns records that fit the where condition can only be unique, the SELECT clause that returns a row returns multiple rows of errors because update can only follow the WHERE clause (inner where) Make a matching update of the corresponding record, only one at a time.
Original point here