CODEWITHSUNDEEP
sqlpostgresql
jaucourt
jaucourt

Reputation: 646

Postgres WITH query and multiple subsequent statements

I need to delete from multiple tables, according to the contents of another table. To avoid having to re-declare a query in every statement, I'm using WITH:

WITH users_to_delete AS (
  SELECT id FROM auth.user WHERE email IN (
    '[email protected]',
    '[email protected]'
  )
)

DELETE FROM schema1.table WHERE "userId" IN (SELECT id FROM users_to_delete);
DELETE FROM schema2.table WHERE "userId" IN (SELECT id FROM users_to_delete);
DELETE FROM schema3.table WHERE "userId" IN (SELECT id FROM users_to_delete);

When trying to execute this, I get an error on the second statement of

relation "users_to_delete" does not exist

Is there a way to re-use the users_to_delete for multiple statements?

Upvotes: 5

Views: 2291

Answers (2)

FatFreddy
FatFreddy

Reputation: 1210

use a helper or temp table:

    begin;

    create temp  table users_to_delete () INHERITS  (auth.user);
    insert into users_to_delete  (SELECT id FROM auth.user WHERE email IN (
        '[email protected]',
        '[email protected]')     );

    DELETE FROM schema1.table WHERE "userId" IN (SELECT id FROM users_to_delete);
    DELETE FROM schema2.table WHERE "userId" IN (SELECT id FROM users_to_delete);
    DELETE FROM schema3.table WHERE "userId" IN (SELECT id FROM users_to_delete);

    ALTER TABLE users_to_delete  NO INHERIT auth.user;
    drop table users_to_delete;
    commit;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269813

Use multiple CTEs:

WITH users_to_delete AS (
      SELECT id
      FROM auth.user
      WHERE email IN ('[email protected]', '[email protected]')
     ),
     d1 AS (
      DELETE FROM schema1.table
      WHERE "userId" IN (SELECT id FROM users_to_delete)
      RETURNING *
     ),
     d2 AS (
      DELETE FROM schema2.table
      WHERE "userId" IN (SELECT id FROM users_to_delete)
      RETURNING *
     )
DELETE FROM schema3.table
WHERE "userId" IN (SELECT id FROM users_to_delete);

The returning clauses are not needed. I just find it strange to have CTEs that are entirely empty.

Upvotes: 11

Related Questions