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)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment