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
FROM B
INNER JOIN D ON B.EMPCODE = D.EMPCODE AND B.SRNO = D.SRNO
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