表t1:
id name id1 id2
1 A 销售
2 B 销售
3 C 厂家
4 d 厂家
表t2:
id1 name id3
23 G 1
24 A 1
25 D 1
26 F 2
如果t1.name=t2.name和t1.id2=“销售”时,把t2.id1更新到t1.id1且t2.id3=1,那表t1变成:id name id1 id2
1 A 24 销售
2 B 销售
3 C 厂家
4 d 厂家
这个sql语句如何写?
id name id1 id2
1 A 销售
2 B 销售
3 C 厂家
4 d 厂家
表t2:
id1 name id3
23 G 1
24 A 1
25 D 1
26 F 2
如果t1.name=t2.name和t1.id2=“销售”时,把t2.id1更新到t1.id1且t2.id3=1,那表t1变成:id name id1 id2
1 A 24 销售
2 B 销售
3 C 厂家
4 d 厂家
这个sql语句如何写?
from t1,t2
where t1.name=t2.name and t1.id2='销售'
drop table t1
go
create table t1
(
id int,
[name] varchar(10),
id1 varchar(10),
id2 varchar(10)
)
go
insert into t1(id,[name],id1,id2)
(
select 1,'A','销售','' union
select 2,'B','销售','' union
select 3,'C','厂家','' union
select 4,'d','厂家',''
)
go
if exists(select * from sysobjects where [name]='t2')
drop table t2
go
create table t2
(
id1 varchar(10),
[name] varchar(10),
id3 varchar(10)
)
go
insert into t2(id1,[name],id3)
(
select '23','G','1' union
select '24','A','1' union
select '25','D','1' union
select '26','F','2'
)
go
update t1 set t1.id1=t2.id1 from t1,t2
where t1.name=t2.name and t1.id2='销售'
go
update t2 set t2.id3=1 from t1,t2
where t1.name=t2.name and t1.id2='销售'
go
if object_id('tempdb.dbo.#t1') is not null drop table #t1
go
create table #t1([id] int,[name] varchar(1),[id1] int,[id2] varchar(4))
insert #t1
select 1,'A',null,'销售' union all
select 2,'B',null,'销售'union all
select 3,'C',null,'厂家'union all
select 4,'d',null,'厂家'--> 测试数据:#t2
if object_id('tempdb.dbo.#t2') is not null drop table #t2
go
create table #t2([id1] int,[name] varchar(1),[id3] int)
insert #t2
select 23,'G',1 union all
select 24,'A',1 union all
select 25,'D',1 union all
select 26,'F',2--------------------------------查询开始------------------------------update [t1] set [t1].[id1]=[t2].[id1] from [#t1] [t1],[#t2] [t2]
where [t1].[name]=[t2].[name]
and [t1].[id2]='销售'
/*
id name id1 id2
----------- ---- ----------- ----
1 A 24 销售
2 B NULL 销售
3 C NULL 厂家
4 d NULL 厂家(4 行受影响)
*/
update t2 set t2.id3=1 from [#t1] [t1],[#t2] [t2]
where t1.name=t2.name and t1.id2='销售'
/*
id1 name id3
----------- ---- -----------
23 G 1
24 A 1
25 D 1
26 F 2(4 行受影响)
*/
from t1,t2
where t1.name=t2.name and t1.id2='销售'