表tableSrc
username age
张三 28
李四 29
黄五 30
钱七 90
赵九 33
表tableAim
username age
张三 0 0
黄五 0 1
赵九 0 0
将表tableAim中的age更新为表tableSrc的age
条件是username相同时更新
- create table tableSrc(
- username varchar (50),
- age int
- )
- create table tableAim(
- username varchar (50),
- age int ,
- sax int
- )
- --delete from tableSrc
- insert into tableSrc(username,age) values('张三',28)
- insert into tableSrc(username,age) values('李四',29)
- insert into tableSrc(username,age) values('黄五',30)
- insert into tableSrc(username,age) values('钱七',90)
- insert into tableSrc(username,age) values('赵九',33)
- insert into tableAim(username,age,sax) values('张三',0,0)
- insert into tableAim(username,age,sax) values('黄五',0,1)
- insert into tableAim(username,age,sax) values('赵九',0,0)
- select * from tableSrc
- select * from tableAim
- update tableAim
set tableAim.age=(select tableSrc.age from tableSrc where tableAim.username=tableSrc.username)
where tableAim.username in (select tableSrc.username from tableSrc where tableAim.username=tableSrc.username)