[PGS9.2] Statement of JOINED UPDATE

/****************************************************************************************************************
-- Title : [PGS9.2] Statement of JOINED UPDATE
-- Reference : dbrang.tistory.com
-- Key word : postgresql joined update 조인된 업데이트 
****************************************************************************************************************/

-- Initialization

DROP TABLE fff;

DROP TABLE fff_import;

 

-- creating table

CREATE TABLE fff (

    id INTEGER NOT NULL UNIQUE PRIMARY KEY,

    name TEXT NOT NULL

);

 

CREATE TABLE fff_import (

    id INTEGER NOT NULL UNIQUE PRIMARY KEY,

    name TEXT NOT NULL

);

 

-- inserting data

INSERT INTO fff VALUES (1, 'aaaaaa');

INSERT INTO fff VALUES (2, 'bbbbbbbbbbbbbb');

INSERT INTO fff VALUES (3, 'cccccccccc');

 

INSERT INTO fff_import VALUES (2, 'UPDATE DATA 11111');

INSERT INTO fff_import VALUES (3, 'UPDATE DATA 2222222');

INSERT INTO fff_import VALUES (4, 'INSERT DATA hong hong');

 

-- confirming data

SELECT 'fff' "TBL", * FROM fff union all

SELECT 'fff_import', * FROM fff_import;

 

/*

-- JOINED UPDATE on SQL Server

*/

-- it's not working normally on PostgreSQL

update fff

set name = b.name

from fff a

inner join fff_import b

on a.id = b.id;

 

select * from fff;  -- on SQL Server

id name

-- -------------------

1  aaaaaa

2  UPDATE DATA 11111

3  UPDATE DATA 2222222

 

select * from fff;  -- on PostgreSQL

id name

-- -------------------

1  "UPDATE DATA 11111"

2  "UPDATE DATA 11111"

3  "UPDATE DATA 11111"

 

/*

-- JOINED UPDATE on PostgreSQL

*/

-- it's working normally on PostgreSQL

-- and occuring error on SQL Server

UPDATE fff AS u

SET name = i.name

FROM fff_import AS i

WHERE u.id = i.id;

 

select * from fff;  -- on PostgreSQL