求SQL更新语句,在线等..

Question

表1 
id   zmd       operator 
1    KDN店       
3    天河店   
4    百佳店 
5    万里店 表2 
zmd       username   password 
KDN店      kdn        233211  
天河店     tiaohe     123456 
万里店     wanli      321122 表1与表2通过zmd相关联的,怎么写SQL语句更新表1的operator,值就是表2的相应的username

Answer 1

--> 测试数据: #1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (id int,zmd varchar(6),operator varchar(50))
insert into #1
select 1,'KDN店',null union all
select 3,'天河店',null union all
select 4,'百佳店',null union all
select 5,'万里店',null
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (zmd varchar(6),username varchar(6),password int)
insert into #2
select 'KDN店','kdn',233211 union all
select '天河店','tiaohe',123456 union all
select '万里店','wanli',321122update a set a.operator=b.username from #1 a join #2 b on a.zmd=b.zmdselect * from #1/*
id          zmd    operator
----------- ------ --------------------------------------------------
1           KDN店   kdn
3           天河店    tiaohe
4           百佳店    NULL
5           万里店    wanli
*/

Answer 2

update 表1 set 表1.operator= 表2.username  from 表2
where 表1.zmd = 表2.zmd

Answer 3

--> 测试数据: #1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (id int,zmd varchar(6),operator varchar(50))
insert into #1
select 1,'KDN店',null union all
select 3,'天河店',null union all
select 4,'百佳店',null union all
select 5,'万里店',null--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (deptname varchar(6),deptnum varchar(6),password int)
insert into #2
select 'KDN店','0001',233211 union all
select '天河店','0005',123456 union all
select '万里店','0003',321122--> 测试数据: #3
if object_id('tempdb.dbo.#3') is not null drop table #3
create table #3 (deptnum varchar(6),username varchar(16))
insert into #3
select '0001','kdn' union all
select '0005','tiaohe' union all
select '0002','wanli'它们的关联是 #1.zmd=#2.deptname   #2.deptnum=#3.deptnum我想用#3.username更新#1.operator，怎么写SQL语句？

Answer 4

--> 测试数据: #1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (id int,zmd varchar(6),operator varchar(50))
insert into #1
select 1,'KDN店',null union all
select 3,'天河店',null union all
select 4,'百佳店',null union all
select 5,'万里店',null--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (deptname varchar(6),deptnum varchar(6),password int)
insert into #2
select 'KDN店','0001',233211 union all
select '天河店','0005',123456 union all
select '万里店','0003',321122--> 测试数据: #3
if object_id('tempdb.dbo.#3') is not null drop table #3
create table #3 (deptnum varchar(6),username varchar(16))
insert into #3
select '0001','kdn' union all
select '0005','tiaohe' union all
select '0002','wanli'update a set a.operator=c.username from #1 a join #2 b on a.zmd=b.deptname join #3 c on b.deptnum=c.deptnum

Answer 5

我想用#3.username更新#1.operator
它们的关联是 #1.zmd=#2.deptname   #2.deptnum=#3.deptnum
-----------
你自己其实就可以写出来了，不用别名：update #1 set operator=#3.username from #1 join #2 on #1.zmd=#2.deptname join #3 on #2.deptnum=#3.deptnum这几个临时表的名字都很短，不用别名也很清晰，实际表不会这么简短，用别名代码简洁清晰一点。

Answer 6

update 表1 set operator=表2.username from 表1 inner join 表2 on 表1.zmd=表2.zmd

Answer 7

update a set 
    operator= b.username  
from 表 a
INNER JOIN  表2 b
on b.zmd = a.zmd

Answer 8

update a set  
    operator= b.username   
from 表1  a 
INNER JOIN  表2 b 
on b.zmd = a.zmd

Answer 9

create table t1
(
id int identity(1,1) not null,
zmd char(10),
operator char(10)
)create table t2
(
zmd char(10),
username char(10),
password char(10)
)insert t2 values('KND','kdn',123)
insert t2 values('KFC','kfc',111)
insert t2 values('UFC','ufc',222)
insert t2 values('UFN','ufn',333)create proc usp_t1_update
(
@zmd char(10)
)
as
begin
declare @operator char(10)
select @operator=username from t2 where zmd=@zmd
update t1 set operator=@operator where zmd=@zmd
end
select * from t1
select * from t2insert t1(zmd) values('KND')exec usp_t1_update 'KND'select * from t1如果用触发器会更简单，不过有时会带来麻烦。

Answer 10

 Create Table tb
       (Id int,
        Zmd Nvarchar(20),
        Operator Nvarchar(20)
        )Create Table tb1
      (Zmd Nvarchar(20),
       Username Nvarchar(20),
       Password Nvarchar(16)
       )Insert tb Values(1,'KDN店','')
Insert tb Values(3,'天河店','')
Insert tb Values(4,'百佳店','')
Insert tb Values(5,'万里店','')Insert tb1 Values('KDN店','kdn','233211')
Insert tb1 Values('天河店','tiaohe','123456')
Insert tb1 Values('万里店','wanli','32112')update tb set Operator=Username
from tb1
where tb.Zmd=tb1.zmdselect * from tb

Answer 11

if object_id(N'table1',N'U') is not null drop table table1
if object_id(N'table2',N'U') is not null drop table table2
create table table1
(
id int,
zmd varchar(50),
operator varchar(50)
)
create table table2
(
zmd varchar(50),
username varchar(50),
[password] varchar(50)
)insert into table1
select 1,'KDN店',null union
select 3,'天河店',null union
select 4,'百佳店',null union
select 5,'万里店',nullinsert into table2
select 'KDN店','kdn','234234' union
select '天河店','tiaohe','w34324' union
select '万里店','wanli','sdfsdf'select * from table1
select * from table2update table1 set table1.operator=table2.username from 
table2  left join table1 on table1.zmd=table2.zmd -------------------结果
（所影响的行数为 4 行）
（所影响的行数为 3 行）id          zmd                                                operator                                           
----------- -------------------------------------------------- -------------------------------------------------- 
1           KDN店                                               NULL
3           天河店                                                NULL
4           百佳店                                                NULL
5           万里店                                                NULL（所影响的行数为 4 行）zmd                                                username                                           password                                           
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
KDN店                                               kdn                                                234234
天河店                                                tiaohe                                             w34324
万里店                                                wanli                                              sdfsdf（所影响的行数为 3 行）
（所影响的行数为 3 行）

Answer 12

晕发没全..现在补充if object_id(N'table1',N'U') is not null drop table table1
if object_id(N'table2',N'U') is not null drop table table2
create table table1
(
id int,
zmd varchar(50),
operator varchar(50)
)
create table table2
(
zmd varchar(50),
username varchar(50),
[password] varchar(50)
)insert into table1
select 1,'KDN店',null union
select 3,'天河店',null union
select 4,'百佳店',null union
select 5,'万里店',nullinsert into table2
select 'KDN店','kdn','234234' union
select '天河店','tiaohe','w34324' union
select '万里店','wanli','sdfsdf'select * from table1
select * from table2update table1 set table1.operator=table2.username from 
table2  left join table1 on table1.zmd=table2.zmd select * from table1
select * from table2（所影响的行数为 4 行）
（所影响的行数为 3 行）id          zmd                                                operator                                           
----------- -------------------------------------------------- -------------------------------------------------- 
1           KDN店                                               NULL
3           天河店                                                NULL
4           百佳店                                                NULL
5           万里店                                                NULL（所影响的行数为 4 行）zmd                                                username                                           password                                           
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
KDN店                                               kdn                                                234234
天河店                                                tiaohe                                             w34324
万里店                                                wanli                                              sdfsdf（所影响的行数为 3 行）
（所影响的行数为 3 行）id          zmd                                                operator                                           
----------- -------------------------------------------------- -------------------------------------------------- 
1           KDN店                                               kdn
3           天河店                                                tiaohe
4           百佳店                                                NULL
5           万里店                                                wanli（所影响的行数为 4 行）zmd                                                username                                           password                                           
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
KDN店                                               kdn                                                234234
天河店                                                tiaohe                                             w34324
万里店                                                wanli                                              sdfsdf（所影响的行数为 3 行）