表1:test1
id name birthid cardid
1 aaa 123
2 bbb 456
3 ccc 789
4 ddd 012 012345
表2:test2
birthid cardid
123 123456
456 456789
789 567890
我怎么通过SQL语句,把test1中的cardid1值改成test1的birthid对应的值,执行结果应该是:
表1:test1
id name birthid cardid
1 aaa 123 123456
2 bbb 456 456789
3 ccc 789 567890
4 ddd 012 012345
多谢了,各位高手!
id name birthid cardid
1 aaa 123
2 bbb 456
3 ccc 789
4 ddd 012 012345
表2:test2
birthid cardid
123 123456
456 456789
789 567890
我怎么通过SQL语句,把test1中的cardid1值改成test1的birthid对应的值,执行结果应该是:
表1:test1
id name birthid cardid
1 aaa 123 123456
2 bbb 456 456789
3 ccc 789 567890
4 ddd 012 012345
多谢了,各位高手!
from test1 a inner join test2 b on a.birthid = b.birthid
insert into test1 values(2 ,'bbb' , '456' ,null)
insert into test1 values(3 ,'ccc' , '789',null)
insert into test1 values(4 , 'ddd' , '012' , '012345')
create table test2 (birthid varchar(5), cardid varchar(10))
insert into test2 values('123','123456')
insert into test2 values('456','456789')
insert into test2 values('789','567890')select * from test2
select *
from test1 a
where a.cardid is not null
union
select a.id,a.name,a.birthid, b.cardid
from test1 a,test2 b
where a.birthid=b.birthid and a.cardid is null
可以用
update test1 set cardid=(select cardid from test2 where test1.birthid=test2.birthid )
where cardid is null or cardid =''
DECLARE @T2 TABLE(BIRTHID INT , CARDID VARCHAR(8))INSERT INTO @T1
SELECT 1, 'aaa', 123, '' UNION ALL
SELECT 2, 'bbb', 456, '' UNION ALL
SELECT 3, 'ccc', 789, '' UNION ALL
SELECT 4, 'ddd', 012, '012345'INSERT INTO @T2
SELECT 123, '123456' UNION ALL
SELECT 456, '456789' UNION ALL
SELECT 789, '567890'UPDATE @T1 SET CARDID = B.CARDID
FROM @T1 A,@T2 B
WHERE A.BIRTHID = B.BIRTHIDSELECT * FROM @T1
WHERE 1 = 1
t2:表2
update t1 set cardid=(select cardid from (select t1.id,t2.cardid from t2,t1 where t1.birthid=t2.birthid) a where a.id=t1.id)
insert into test1 values(2 ,'bbb' , '456' ,null)
insert into test1 values(3 ,'ccc' , '789',null)
insert into test1 values(4 , 'ddd' , '012' , '012345')
create table test2 (birthid varchar(5), cardid varchar(10))
insert into test2 values('123','123456')
insert into test2 values('456','456789')
insert into test2 values('789','567890')
--
-- select * from test2
--
--
-- select *
-- from test1 a
-- where a.cardid is not null
-- union
-- select a.id,a.name,a.birthid, b.cardid
-- from test1 a,test2 b
-- where a.birthid=b.birthid and a.cardid is nullupdate test1 set cardid=b.cardid
from test1 a join test2 b on a.birthid=b.birthid
update test1 set test1.cardid=test2.cardid from test1,test2 where test1.birthid=test2.birthid
insert into test1 values(2 ,'bbb' , '456' ,null)
insert into test1 values(3 ,'ccc' , '789',null)
insert into test1 values(4 , 'ddd' , '012' , '012345')
create table test2 (birthid varchar(5), cardid varchar(10))
insert into test2 values('123','123456')
insert into test2 values('456','456789')
insert into test2 values('789','567890')
update test1
set
cardid=(select test2.cardid from test2 where test1.birthid=test2.birthid)
where cardid is null
select * from test1
----------- ----- ------- ----------
1 aaa 123 123456
2 bbb 456 456789
3 ccc 789 567890
4 ddd 012 012345
set cardid = (select cardid from test2 t2 where t2.birthid = t1.birthid )
from test1 t1 where t1.cardid is null or t1.cardid = ''
update test1 set t1.cardid = t2.cardid
from test1 t1, test2 t2
where t1.birthid = t2.birthid and t1.cardid is null or t1.cardid = ''