在一个查询中使用联接更新多个表 [英] updating multiple tables using joins in one query

问题描述

大家好，

我有一种情况，我需要使用联接在一个更新语句中更新2个表.有人可以帮我吗?

以下是示例方案.

Hi All ,

I have a scenario where I need to update 2 tables in one update statement using joins. Can anybody help me out with this.

Below is the sample scenario.

DROP TABLE B
DROP TABLE D


CREATE TABLE B
(EMPCODE VARCHAR(20), SRNO INT, SALARY NUMERIC, Remarks varchar(100))

CREATE TABLE D
(EMPCODE VARCHAR(20), SRNO INT, SALARY NUMERIC, Remarks varchar(100))


INSERT INTO B
VALUES(1, 3, 17000,'')
INSERT INTO B
VALUES(1, 1, 15000,'')
INSERT INTO B
VALUES(1, 2, 16000,'')
INSERT INTO B
VALUES(2, 1, 15000,'')
INSERT INTO B
VALUES(2, 2, 16000,'')

INSERT INTO D
VALUES(1, 3, 17,'AAAA')
INSERT INTO D
VALUES(1, 1, 15,'bbbb')
INSERT INTO D
VALUES(1, 2, 16,'vvvv')
INSERT INTO D
VALUES(2, 1, 15,'dddd')
INSERT INTO D
VALUES(2, 2, 16,'hhhh')


SELECT * FROM B
SELECT * FROM D


SELECT *
FROM B
INNER JOIN D ON B.EMPCODE = D.EMPCODE AND B.SRNO = D.SRNO


UPDATE D
SET D.SALARY = B.SALARY--, B.SALARY = D.SALARY
FROM B
INNER JOIN D ON B.EMPCODE = D.EMPCODE AND B.SRNO = D.SRNO

表D的注释应在表B和
中进行更新 表B的薪金应在表D中更新


有人可以帮我吗?

问候，
Gopal

Remarks of table D should be updated in table B and
Salary of Table B should be updated in Table D


Can anybody help me out with this.

Regards,
Gopal

推荐答案

您无法执行此操作，您一次只能更新一个表.

但是，您可以使用事务 [

You cannot do that, you can only update one table at the time.

You can however use a transaction[^] so that you ensure both tables are update at the same time.

BEGIN TRAN T1

	-- Update Remarks
	UPDATE D
	SET D.Remarks = B.Remarks
	FROM D
	INNER JOIN B ON B.EMPCODE = D.EMPCODE AND B.SRNO = D.SRNO

	-- Update SALARY
	UPDATE B
	SET B.SALARY= D.SALARY
	FROM B
	INNER JOIN D ON D.EMPCODE = B.EMPCODE AND D.SRNO = B.SRNO

COMMIT TRAN T1

这篇关于在一个查询中使用联接更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！