0

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?

Community
  • 1
  • 1
Mio
  • 1,412
  • 2
  • 19
  • 41

1 Answers1

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;
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470