34,571
社区成员
发帖
与我相关
我的任务
分享
update b set upid=(select aid from a where a.id=b.id)
create table a(id int,aid varchar(5))
insert into a
select 1,'01' union all
select 2,'01'
go
create table b(id int,upid varchar(5))
insert into b
select 1,'01' union all
select 1,'03' union all
select 2,'02' union all
select 2,'02' union all
select 2,'02'
go
update b
set b.upid = a.aid
from a, b
where b.id = a.id and b.upid <> a.aid
select *
from b
drop table a,b
/***********
id upid
----------- -----
1 01
1 01
2 01
2 01
2 01
(5 行受影响)
update b
set b.upid = a.aid
from a, b
where b.id = a.id and b.upid <> a.aid
update b
set b.upid = a.aid
from a, b
where b.id = a.id and b.upid <> a.aid
update b
set b.upid = a.aid
from table_a a,table_b b
where b.id = a.id and b.upid <> a.aid
CREATE TABLE A
(
id INT IDENTITY(1,1),
[name] VARCHAR(10),
aid VARCHAR(10)
)
INSERT INTO A
SELECT 'a','01'
UNION ALL
SELECT 'b','01'
CREATE TABLE bge
(
id INT,
upid VARCHAR(10),
[value] INT
)
INSERT INTO bge
SELECT 1 ,'01', 1
UNION ALL
SELECT 1 ,'01', 2
UNION ALL
SELECT 2 ,'02', 1
SELECT * FROM bge
SELECT * FROM a
id upid value
----------- ---------- -----------
1 01 1
1 01 2
2 02 1
(3 row(s) affected)
id name aid
----------- ---------- ----------
1 a 01
2 b 01
(2 row(s) affected)
UPDATE bge SET upid=(SELECT aid FROM a WHERE id=bge.id)
SELECT * FROM bge
id upid value
----------- ---------- -----------
1 01 1
1 01 2
2 01 1
(3 row(s) affected)