How to UPDATE a table with data from another table in SQL
- How-Tos FAQs
- December 17, 2018
We can UPDATE a table with data from any other table in SQL.
Let us consider the following two tables.
CREATE TABLE student_old (
student_id INT ,
student_name VARCHAR(50),
major VARCHAR(50),
batch INT
);
CREATE TABLE student_new (
student_id INT ,
student_name VARCHAR(50),
major VARCHAR(50),
batch INT
);
INSERT INTO student_old(student_id, student_name, major, batch)
VALUES (1, 'Jack', 'Arts', 2010);
INSERT INTO student_old(student_id, student_name, major, batch)
VALUES (2, 'Rose', 'Computer', 2012);
INSERT INTO student_old(student_id, student_name, major, batch)
VALUES (3, 'John', 'Economics', 2018);
INSERT INTO student_old(student_id, student_name, major, batch)
VALUES (4, 'Joe', 'Robotics', 2015);
INSERT INTO student_new(student_id, student_name, major, batch)
VALUES (1, 'Jack', 'Chemistry', 2016);
INSERT INTO student_new(student_id, student_name, major, batch)
VALUES (2, 'Rose', 'Medicine', 2017);
INSERT INTO student_new(student_id, student_name, major, batch)
VALUES (3, 'John', 'History', 2020);
SELECT * FROM student_old;
-- Output
student_id student_name major batch
---------------------------------------------------
1 Jack Arts 2010
2 Rose Computer 2012
3 John Economics 2018
4 Joe Robotics 2015
SELECT * FROM student_new;
-- Output
student_id student_name major batch
-----------------------------------------------------
1 Jack Chemistry 2016
2 Rose Medicine 2017
3 John History 2020To UPDATE data from table ‘student_old’ using data from table ‘student_new,’ we can use the following UPDATE QUERY in different Databases.
Oracle
UPDATE student_old o
SET (major, batch) = (
SELECT n.major, n.batch
FROM student_new n
WHERE n.student_id = o.student_id
)
WHERE EXISTS (
SELECT 1
FROM student_new n
WHERE n.student_id = o.student_id
);
SELECT * FROM student_old;
-- Output
-- Only the matched row were updated
student_id student_name major batch
---------------------------------------------------
1 Jack Chemistry 2016
2 Rose Medicine 2017
3 John History 2020
4 Joe Robotics 2015SQL Server
UPDATE o
SET major = n.major,
batch = n.batch
FROM student_old o
INNER JOIN student_new n
ON o.student_id = n.student_id
SELECT * FROM student_old;
-- Output
-- Only the matched row were updated
student_id student_name major batch
------------------------------------------------
1 Jack Chemistry 2016
2. Rose Medicine 2017
3 John History 2020
4 Joe Robotics 2015And that’s how to UPDATE a table with data from another table in SQL!
Up Next:
Read How to use GROUP BY on multiple columns of a table in SQL
