Search

Friday 7 June 2019

Postgres Update and Delete statements involving a join

The high-level pg-sql commands below show the syntax for updating or deleting from a postgres table.



--POSTGRES
--DML
--UPDATE
update tbl_target m
set sales = d.sales
,   status= d.status
from tbl_source d
where m.id = d.id;

--DELETE
delete from tbl_target a
using tbl_source b
where a.id = b.id;



The SQL SERVER equivalent is:

--SQL SERVER
--DML
--UPDATE

update tbl_target
set sales = b.sales
, status = b.status
from tbl_source b
where tbl_target.id = b.id;

delete tbl_target
from tbl_source b
where b.id = tbl_target.id;




Below – the setup scripts for the tables (works for both postgres and sql server)

create table tbl_target (id int, sales int, status varchar(10));
insert into tbl_target values
(1,10,'red'),
(2,20,'yellow'),
(3,30,'green');

create table tbl_source (id int, sales int, status varchar(10));
insert into tbl_source values
(3,35,'dark-green');

select * from tbl_target;
select * from tbl_source;
/*
drop table tbl_target;
drop table tbl_source;
*/

No comments:

Post a Comment