--跨表连查更新
create table m_name (id int,name varchar(4))
insert into m_name
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
select * from m_name
/*
id name
----------- ----
1 张三
2 李四
3 王五
*/
create table m_chengji (name varchar(4),kemu int,chengji int,id sql_variant)
insert into m_chengji
select '张三',1,95,null union all
select '张三',2,92,null union all
select '张三',3,91,null union all
select '李四',1,56,null union all
select '李四',2,76,null union all
select '李四',3,99,null union all
select '王五',1,57,null union all
select '王五',2,100,null union all
select '王五',3,67,null
select * from m_chengji
/*
name kemu chengji id
---- ----------- ----------- -----------
张三 1 95 NULL
张三 2 92 NULL
张三 3 91 NULL
李四 1 56 NULL
李四 2 76 NULL
李四 3 99 NULL
王五 1 57 NULL
王五 2 100 NULL
王五 3 67 NULL
*/
update m_chengji
set id = a.id from m_chengji b left join m_name a on a.[name]=b.[name]
UPDATE B SET COL1=A.COL1,COL2=A.COL2 ,.. FROM A WHERE A.ID=B.ID
INSERT B (ID,COL1,COL2...)SELECT ID,COL1,COL2... FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=A.ID)
UPDATE B SET COL1=A.COL1,COL2=A.COL2 ,.. FROM A WHERE A.ID=B.ID
if exists(select [Name] from tb1 where [Name]='张三')
update tb1 set [Phone]='123' where [Name]='张三'
else
insert tb1 values('张三','123')
end
if exists(select [Name] from tb1 where [Name]='张三')
update tb1 set [Phone]='123' where [Name]='张三'
else
insert tb1 values('张三','123')
end
if not exists(select * from Talble_A where [ID]=@ID)
insert Table_A (--要插入的列--) select --要插入的列-- from Talbe_B where [ID]=@ID
