JOIN 在 INSERT、UPDATE、DELETE 中的使用

JOIN 在 INSERT、UPDATE、DELETE 中的使用

通常我们的 JOIN 语句都是用在 SELECT 上面，但其实也可以用在 INSERT、UPDATE、DELETE 上面。

表关联插入（INSERT + SELECT JOIN）

-- 表关联插入
INSERT INTO [dbo].[Table_1](A,B,C,D,E)
SELECT T2.X, T2.Y, T2.Z, 'OKKKKK', T1.C
FROM [dbo].[Table_1] T1
JOIN [dbo].[Table_2] T2 ON T1.A = T2.X
WHERE 1 = 1Code language: JavaScript (javascript)

INSERT INTO Table_1(A,B,C,D,E)
SELECT s.stu_id, s.stu_name, s.stu_gender, s.stu_age, t.B
FROM [Test].[dbo].[student] s
JOIN Table_1 t ON s.stu_name = t.A
WHERE s.stu_gender = '女'Code language: JavaScript (javascript)

表关联更新（UPDATE + JOIN）

-- 常见的更新
UPDATE [dbo].[Table_1]
SET [A] = 'a'
WHERE 1 = 1Code language: JavaScript (javascript)

-- 带上别名的 Update
UPDATE T
SET T.[A] = 'b'
FROM [dbo].[Table_1] T
WHERE 1 = 1Code language: JavaScript (javascript)

-- 表关联 Update
UPDATE T1
SET T1.[B] = T2.Y -- 使用另外一张表的字段
FROM [dbo].[Table_1] T1
JOIN [dbo].[Table_2] T2 ON T1.A = T2.X
WHERE 1 = 1

-- 查询验证
SELECT * FROM [dbo].[Table_1]Code language: CSS (css)

表关联删除（DELETE + JOIN）

-- 表关联删除
DELETE T1
FROM [dbo].[Table_1] T1
JOIN [dbo].[Table_2] T2 ON T1.A = T2.X
WHERE 1 = 1

SELECT * FROM [dbo].[Table_1]
SELECT * FROM [dbo].[Table_2]Code language: CSS (css)

-- 常用的删除语句
DELETE FROM [dbo].[Table_1] WHERE A = 'BMWX'Code language: JavaScript (javascript)

-- 带别名的删除语句，如果要删除的表带别名，则 DELETE 后面要带上别名
DELETE T FROM [dbo].[Table_1] T WHERE T.A = 'BMWX'Code language: JavaScript (javascript)

-- 表关联删除（完整写法）
DELETE T 
-- SELECT *  
FROM [dbo].[Table_1] T 
JOIN [dbo].[Table_2] T2 ON T.A = T2.A
WHERE T.A = 'BMWX' OR 1=1Code language: JavaScript (javascript)