Thursday, July 26, 2007

Merge statement not based on another source table

CREATE TABLE test_table (name VARCHAR(10), cnt INT);

-- MS SQL Server 2008 allows select without from

merge into test_table T
USING (select 'ABC' as name, 5 as cnt) S
ON (S.name = T.name)
WHEN matched THEN
UPDATE SET T.cnt = T.cnt + S.cnt
WHEN NOT matched THEN
INSERT (name, cnt) VALUES (S.name, S.cnt)

-- or using table value constructor

merge into test_table T
USING (values('ABC', 5)) as S(name,cnt)
ON (S.name = T.name)
WHEN matched THEN
UPDATE SET T.cnt = T.cnt + S.cnt
WHEN NOT matched THEN
INSERT (name, cnt) VALUES (S.name, S.cnt)

-- Oracle must select from dual

merge into test_table T
USING (select 'ABC' as name, 5 as cnt from dual) S
ON (S.name = T.name)
WHEN matched THEN
UPDATE SET T.cnt = T.cnt + S.cnt
WHEN NOT matched THEN
INSERT (name, cnt) VALUES (S.name, S.cnt)