Re: delete from multiple tables

Hans Ginzel <hans(at)matfyz(dot)cz> wrote:

> Hello!
>
> How to delete from multiple tables, please?
>
> DELETE t1, t2
> FROM table1 AS t1
> JOIN table2 AS t2 ON t1.fkey = t2.pkey
> WHERE ...
>
> Best regards
> Hans

You can use writeable common table expressions (wCTE):

test=*# select * from t1;
id | val
----+-----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
(5 rows)

Time: 0,155 ms
test=*# select * from t2;
id | val
----+-----
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
(6 rows)

Time: 0,161 ms
test=*# with del as (select t2.id from t1 left join t2 on t1.id=t2.id where t2.id is not null), del_t1 as (delete from t1 where id in (select id from del)), del_t2 as (delete from t2 where id in (select id from del)) select 'using writeale common table expressions';
select * from t1;
?column?
-----------------------------------------
using writeale common table expressions
(1 row)

Time: 12,240 ms
id | val
----+-----
1 | 1
2 | 2
(2 rows)

Time: 0,088 ms
test=*#
test=*#
test=*#
test=*# select * from t2;
id | val
----+-----
6 | 6
7 | 7
8 | 8
(3 rows)

Time: 0,152 ms

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°