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%'
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.
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
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;
