有一表T
col_htbh col_fdbh
H001
H002 H001表T-1
col_htbh col_cpbh img
H001 A 1.jpg
H001 B 2.jpg
H002 A
H002 B
如何更新表T-1 字段img的数据为col_htbh col_cpbh img
H001 A 1.jpg
H001 B 2.jpg
H002 A 1.jpg
H002 B 2.jpg条件是T表中的数据字段col_htbh为H2的相对应的col_fdbh为H1
如何写这个SQL语句?
col_htbh col_fdbh
H001
H002 H001表T-1
col_htbh col_cpbh img
H001 A 1.jpg
H001 B 2.jpg
H002 A
H002 B
如何更新表T-1 字段img的数据为col_htbh col_cpbh img
H001 A 1.jpg
H001 B 2.jpg
H002 A 1.jpg
H002 B 2.jpg条件是T表中的数据字段col_htbh为H2的相对应的col_fdbh为H1
如何写这个SQL语句?
from (select col_htbh,col_cpbh,img from t where right(col_htbh,2) = '01') a
where left(a.col_htbh,2) + '02' = t.col_htbh and a.col_cpbh = t.col_cpbh
SET a.img=b.img
FROM [表T-1] a,
(SELECT c.col_htbh, f.img
FROM [表T-1] c, [表T] d, [表T] e, [表T-1] f
WHERE c.img=''
AND c.col_htbh=d.col_htbh
AND d.col_fdbh=e.col_htbh
AND e.col_htbh=f.col_htbh) b
WHERE a.img=''
AND a.col_htbh=b.col_htbh
条件是T表中的数据字段col_htbh为H002的相对应的col_fdbh为H001
也就是说H002对应的col_fdbh为H001
就是把表T-1表中col_htbh为H002的img字段的值更新为col_htbh为H001的img的值
如何写?
insert @ta
select 'H001',null union all
select 'H002' , 'H001'declare @tb table(col_htbh varchar(10), col_cpbh varchar(10), img varchar(10))
insert @tb
select 'H001', 'A', '1.jpg' union all
select 'H001', 'B', '2.jpg' union all
select 'H002', 'A', null union all
select 'H002', 'B', null--更新
update b
set b.img=c.img
from @tb b join @tb c on b.col_cpbh=c.col_cpbh join @ta a on a.col_htbh=b.col_htbh
where b.col_htbh!=c.col_htbh and
exists(select 1 from @ta where col_htbh=a.col_fdbh)
--查询
select * from @tb(所影响的行数为 2 行)
(所影响的行数为 4 行)
(所影响的行数为 2 行)col_htbh col_cpbh img
---------- ---------- ----------
H001 A 1.jpg
H001 B 2.jpg
H002 A 1.jpg
H002 B 2.jpg(所影响的行数为 4 行)
insert T select 'H001', null
union all select 'H002', 'H001'create table T2(col_htbh nvarchar(10), col_cpbh char(1), img nvarchar(10))
insert T2 select 'H001', 'A', '1.jpg'
union all select 'H001', 'B', '2.jpg'
union all select 'H002', 'A',null
union all select 'H002', 'B',null update T2 set img=tmp.img
from(
select T.col_htbh, T.col_fdbh, T2.col_cpbh, T2.img
from T, T2 where T.col_fdbh=T2.col_htbh) as tmp
where T2.col_htbh=tmp.col_htbh and T2.col_cpbh=tmp.col_cpbhselect * from T2--result
col_htbh col_cpbh img
---------- -------- ----------
H001 A 1.jpg
H001 B 2.jpg
H002 A 1.jpg
H002 B 2.jpg(4 row(s) affected)
insert ta
select 'H001',null union all
select 'H002' , 'H001'create table tb(col_htbh varchar(10), col_cpbh varchar(10), img varchar(10))
insert tb
select 'H001', 'A', '1.jpg' union all
select 'H001', 'B', '2.jpg' union all
select 'H002', 'A', null union all
select 'H002', 'B', null--更新
update b
set b.img=c.img
from tb b join tb c on b.col_cpbh=c.col_cpbh join ta a on a.col_htbh=b.col_htbh
where b.col_htbh!=c.col_htbh and
exists(select 1 from ta where col_htbh=a.col_fdbh)
--查询
select * from tb
col_htbh col_cpbh img
---------- ---------- ----------
H001 A 1.jpg
H001 B 2.jpg
H002 A 1.jpg
H002 B 2.jpg(所影响的行数为 4 行)