설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


Powered by EnterpriseDB
총 게시물 78건, 최근 0 건
 

Oracle merge 문 대체구문 - on conflict

글쓴이 : PostgresDBA 날짜 : 2017-08-09 (수) 10:00 조회 : 37

drop table customers;

CREATE TABLE customers (
name VARCHAR not null,
email VARCHAR NOT NULL,
active bool NOT NULL DEFAULT TRUE,
constraint pk_customer primary key(name, email)
);


INSERT INTO customers (NAME, email)
VALUES
('IBM', 'ibm@postgresdba.com'),
(
'Microsoft',
'ms@postgresdba.com'
),
(
'Intel',
'intel@postgresdba.com'
);

INSERT INTO customers (name, email)
VALUES
(
'Microsoft',
'ms@postgresdba.com'
)
ON CONFLICT (name,email)   -- 컬럼명 말고  pk constraint 명시도 가능
DO
UPDATE
  SET email = EXCLUDED.email || '==> new_' || customers.email;

SQL> select * From customers;
+-----------+----------------------------------------------+--------+
|   name    |                    email                     | active |
+-----------+----------------------------------------------+--------+
| IBM       | ibm@postgresdba.com                          | t      |
| Intel     | intel@postgresdba.com                        | t      |
| Microsoft | ms@postgresdba.com==> new_ms@postgresdba.com | t      |
+-----------+----------------------------------------------+--------+
(3 rows)


 

postgresdba.com