Move data from one table to another with Redshift

Question

I wanted to move log datas that have a specific user_id to a new table on Redshift. I've started playing with WITH block like :

WITH moved_rows AS (
    DELETE FROM sensor_log_enable
    USING sensor_log_disable
    WHERE sensor_log_enable.user_id
       IN (16,17,18)
    RETURNING sensor_log_enable.*
)
INSERT INTO sensor_log_disable
SELECT * FROM moved_rows;

But redshift doesn't like it.

ERROR:  syntax error at or near "DELETE"
LINE 2:     DELETE FROM active_connections

Redshift doesn't seem to include DELETE in WITH block. What's the best strategy then ? 1 INSERT INTO then an INNER JOIN OR LEFT OUTER JOIN with DELETE?

John Rotenstein · Accepted Answer · 2016-11-29 06:52:00Z

1

To 'move' data between tables, you must:

Use INSERT INTO to copy the data to the target table
Use DELETE to delete the data from the source table

They must be performed as separate SQL commands. You can, however, wrap those commands in BEGIN/END statements to commit them as one transaction:

BEGIN;
INSERT INTO...;
DELETE FROM...;
END;

answered Nov 29, 2016 at 6:52

John Rotenstein

273k28 gold badges456 silver badges541 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mio Over a year ago

Thanks John for you answer.

user433342 · Accepted Answer · 2024-10-23 14:36:09Z

0

If you wanna move the whole table use alter table append

answered Oct 23, 2024 at 14:36

user433342

1,1081 gold badge13 silver badges36 bronze badges

Collectives™ on Stack Overflow

Move data from one table to another with Redshift

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related