DEV Community

Theoklitos Bampouris
Theoklitos Bampouris

Posted on

SQL: Copy Tables and Massive Update

The road map

There are a lot of times during development where we make a mistake and all or part of our data are gone. Even worse when we lose them due to a bug in our production version.

In this post, we'll see how to create a database table as a backup from an existing one, copy the data, and make a massive update joining these two tables. We'll quote the corresponding SQL statements from different RDBMSs: MariaDB, PostgreSQL, MS SQL Server, IBM DB2, SQLite.

MariaDB

Create the table

CREATE TABLE tbl2 LIKE tbl1;
Enter fullscreen mode Exit fullscreen mode

tbl2 table will inhert all the attributes from tbl1.

Copy the data

INSERT INTO tbl2
SELECT * FROM tbl1;
Enter fullscreen mode Exit fullscreen mode

We may use the WHERE clause to insert only a part of tbl1's data.

Massive update

UPDATE tbl1 a
INNER JOIN tbl2 b ON
a.key = b.key
SET a.column1 = b.column1;
Enter fullscreen mode Exit fullscreen mode

PostgreSQL

Create the table

CREATE TABLE tbl2 ( LIKE tbl1 INCLUDING ALL );
Enter fullscreen mode Exit fullscreen mode

Copy the data

INSERT INTO tbl2
SELECT * FROM tbl1;
Enter fullscreen mode Exit fullscreen mode

Massive update

UPDATE tbl1 a
SET a.column1 = b.column1
FROM tbl2 b
WHERE
a.key = b.key;
Enter fullscreen mode Exit fullscreen mode

MS SQL Server

Create the table and copy all data

SELECT * INTO tbl2
FROM tbl1
Enter fullscreen mode Exit fullscreen mode

Create the table ONLY

SELECT * INTO tbl2
FROM tbl1
WHERE 1 = 0;
Enter fullscreen mode Exit fullscreen mode

Massive update

UPDATE a
SET a.column1 = b.column1
FROM tbl1 a
INNER JOIN tbl2 b ON
a.key = b.key;
Enter fullscreen mode Exit fullscreen mode

IBM DB2

Create the table

CREATE TABLE tbl2 LIKE tbl1;
Enter fullscreen mode Exit fullscreen mode

Copy the data

INSERT INTO tbl2
SELECT * FROM tbl1;
Enter fullscreen mode Exit fullscreen mode

Massive update

UPDATE tbl1 a
SET a.column1 = b.column1
FROM tbl2 b
WHERE
a.key = b.key;
Enter fullscreen mode Exit fullscreen mode

SQLite

Create the table only

CREATE TABLE tbl2 AS
SELECT * FROM tbl1
WHERE 1 = 0;
Enter fullscreen mode Exit fullscreen mode

Copy the data only

INSERT INTO tbl2
SELECT * FROM tbl1;
Enter fullscreen mode Exit fullscreen mode

Create the table and the data at the same time

CREATE TABLE tbl2 AS
SELECT * FROM tbl1;
Enter fullscreen mode Exit fullscreen mode

Massive update

UPDATE tbl1
SET
column1 = (SELECT b.column1
    FROM tbl2 b
    WHERE b.key = tbl1.key)
WHERE tbl1.key IN (SELECT b.key FROM tbl2 b);
Enter fullscreen mode Exit fullscreen mode

In SQLite we may take advantage of the Row Values Method, in the case we want to use more than one key:

UPDATE tbl1
SET
column1 = (SELECT b.column1
    FROM tbl2 b
    WHERE (b.key1, b.key2) = (tbl1.key1, tbl1.key2) )
WHERE (tbl1.key1, tbl1.key2) IN (
  SELECT b.key1, b.key2 FROM tbl2 b);
Enter fullscreen mode Exit fullscreen mode

Originally published at https://www.codingnotebook.eu/sql-copy-tables-and-massive-update/

Top comments (2)

Collapse
 
helenanders26 profile image
Helen Anderson

Thank you for the handy scripts. Bookmarked to have on hand when I need to do this on a new platform.

:)

Collapse
 
theoklitosbam7 profile image
Theoklitos Bampouris

Nice to hear that Helen. Thank you! :-)