Steps to Update Two Tables in One Statement in SQL Server

Follow this step-by-step guide, where we create two tables and update them in one statement.

Step 1: Create a Database.

Query:

CREATE DATABASE w3wiki

Output:

Step 2: Use the database.

Query:

USE w3wiki

Output:

Step 3: Create a table ECONOMICS_MARKS inside the database w3wiki. This table has 3 columns namely ID, S_NAME and ECO_MARKS containing the roll number and name of the students and the marks scored by the students in economics subject.

Query:

CREATE TABLE ECONOMICS_MARKS(
ID INT,
S_NAME VARCHAR(10),
ECO_MARKS INT);

Output:

Step 4: Describe the structure of the table ECONOMICS_MARKS.

Query:

EXEC SP_COLUMNS ECONOMICS_MARKS;

Output:

Step 5: Create a table COMMERCE_MARKS inside the database w3wiki. This table has 3 columns namely ID, S_NAME and COM_MARKS containing the roll number and name of the students and the marks scored by the students in commerce subject.

Query:

CREATE TABLE COMMERCE_MARKS(
ID INT,
S_NAME VARCHAR(10),
COM_MARKS INT);

Output:

Step 6: Describe the structure of the table COMMERCE_MARKS.

Query:

EXEC SP_COLUMNS COMMERCE_MARKS;

Output:

Step 7: Insert 5 rows into the ECONOMICS_MARKS table.

Query:

INSERT INTO ECONOMICS_MARKS VALUES (1,'SAM',70);
INSERT INTO ECONOMICS_MARKS VALUES (2,'AMY',68);
INSERT INTO ECONOMICS_MARKS VALUES (3,'EMMA',69);
INSERT INTO ECONOMICS_MARKS VALUES (4,'ROB',57);
INSERT INTO ECONOMICS_MARKS VALUES (5,'KEVIN',65);

Output:

Step 8: Display all the rows of the ECONOMICS_MARKS table.

Query:

SELECT * FROM ECONOMICS_MARKS;

Output:

Step 9: Insert 5 rows into the COMMERCE_MARKS table.

Query:

INSERT INTO COMMERCE_MARKS VALUES (1,'SAM',80);
INSERT INTO COMMERCE_MARKS VALUES (2,'AMY',88);
INSERT INTO COMMERCE_MARKS VALUES (3,'EMMA',90);
INSERT INTO COMMERCE_MARKS VALUES (4,'ROB',75);
INSERT INTO COMMERCE_MARKS VALUES (5,'KEVIN',56);

Output:

Step 10: Display all the rows of the COMMERCE_MARKS table.

Query:

SELECT * FROM COMMERCE_MARKS;

Output:

Step 11: Update the economics and commerce marks of student having id=1 to 80 and 75 respectively using a single query. This involves 2 steps. First, perform JOIN of ECONOMICS_MARKS table and COMMERCE_MARKS table. Then using ALIASES of the tables which are E and C respectively, compare the ID of students(to ensure same ID is picked from both tables) AND finally compare the student ID to 1(given value). When both these conditions fulfill, UPDATE the corresponding marks to 80 and 75. This whole thing must be enclosed between BEGIN TRANSACTION and COMMIT to treat it a single ATOMIC operation. This query involves updating of records belonging to the same student IDs.

Query:

BEGIN TRANSACTION;
UPDATE ECONOMICS_MARKS
SET ECONOMICS_MARKS.ECO_MARKS = 80
FROM ECONOMICS_MARKS E, COMMERCE_MARKS C
WHERE E.ID = C.ID
AND E.ID = 1;
UPDATE COMMERCE_MARKS
SET COMMERCE_MARKS.COM_MARKS = 75
FROM ECONOMICS_MARKS E, COMMERCE_MARKS C
WHERE E.ID = C.ID
AND C.ID = 1;
COMMIT;

Output:

Step 12: Display all the rows of the updated ECONOMICS_MARKS table.

Query:

SELECT * FROM ECONOMICS_MARKS;

Note – The value of the column ECO_MARKS for the ID 1 is updated to 80.

Output:

Step 13: Display all the rows of the updated COMMERCE_MARKS table.

Query:

SELECT * FROM COMMERCE_MARKS;

Note: The value of the column COM_MARKS for the ID 1 is updated to 75.

Output:

To update two tables in one statement in SQL Server, use the BEGIN TRANSACTION clause and the COMMIT clause. The individual UPDATE clauses are written in between the former ones to execute both updates simultaneously.

Here, we will learn how to update two tables in a single statement in SQL Server.