Re: delete from multiple tables

Hans Ginzel <hans@matfyz.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
t1where id in (select id from del)), del_t2 as (delete from t2 where id in (select id from del)) select 'using writeale
commontable 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°