CODEWITHSUNDEEP
sqlpostgresqlpgadmin
Nikolay Kuznetsov
Nikolay Kuznetsov

Reputation: 9579

SELECT INTO a variable

I have significant number of commands like:

DELETE FROM table1 WHERE user_id IN (SELECT id FROM users WHERE email LIKE 'abc%');
DELETE FROM table2 WHERE user_id IN (SELECT id FROM users WHERE email LIKE 'abc%');
DELETE FROM table3 WHERE user_id IN (SELECT id FROM users WHERE email LIKE 'abc%');

How can I optimize it by extraction in a variable/list/array/set?

SELECT id FROM users WHERE email LIKE 'abc%'

Upvotes: 0

Views: 171

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

Use data-modifying CTEs like @a_horse suggested. But join in the derived table from first CTE in the DELETE statements with the USING clause. That's a bit shorter and faster:

WITH sel  AS (SELECT id FROM users WHERE email LIKE 'abc%')
,    del1 AS (DELETE FROM table_1 t USING sel WHERE t.user_id = sel.id)
,    del2 AS (DELETE FROM table_2 t USING sel WHERE t.user_id = sel.id) 
              DELETE FROM table_3 t USING sel WHERE t.user_id = sel.id;

Upvotes: 0

Nebojsa Susic
Nebojsa Susic

Reputation: 1260

You can create temp table as

CREATE TEMP TABLE user_for_delete as
SELECT id 
FROM users 
WHERE email 
LIKE 'abc%';

and for any additional condition 

INSERT INTO user_for_delete (id)
SELECT id 
FROM users 
WHERE email 
LIKE 'cdb%';

and finally delete from table1,2,3

DELETE FROM table1 t
USING user_for_delete u
WHERE u.id = t.user_id

Upvotes: 2

user330315
user330315

Reputation:

If your goal is to only write the condition on which user to delete only once, you can use a data modifying CTE:

with user_list as (
   select id
   from users
   where email like 'abc%'
), delete_1 as (
   delete from table_1 where user_id in (select id from user_list)
), delete_1  as (
   delete from table_2 where user_id in (select id from user_list)
) 
delete from table_3 where user_id in (select id from user_list);

You still need to repeat the select ... for each delete but the condition on what needs to be deleted is only once in your statement.

Upvotes: 3

Related Questions