Hi all!
I would like to use the db2 merge statement submitting it as a
statement from jdbc. I am in the following scenario. I'm working with
a proprietary persistence layer and I'm handling an entity I don't
know whether it's already persisted or not and I would like to use the
merge statement in order to insert or update a row on the database. Is
it possible? Suppose I'm working with the table people with three
columns: id, name, surname and I'm handling an entity with id="5",
name="chuck", surname="norris" Am I able to issue:

MERGE INTO people AS t
USING (select '5' as id, 'chuck' as name, 'norris' as surname from
SYSIBM.SYSDUMMY1)As s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.name=s.name, t.surmane=s.surname
WHEN NOT MATCHED THEN
INSERT
(id, name, surname)
VALUES (s.id, s.name, s.surname)

such a statement? I'm trying to do that but I got an error. I don't
think it's allowed to use a select after USING:

USING (select '5' as id, 'chuck' as name, 'norris' as surname from
SYSIBM.SYSDUMMY1)As s

I also tryed to do:

USING VALUES('5','chuck','norris') AS s(id,chuck,norris)

but maybe I was wrong with the syntax and it dosn't work. Any help
would be appreciated.
Thanks Fil

What error message(s) did you got? What were exact error code and
text?

What are your DB2 version/release and platform OS?

On Feb 15, 1:53*pm, phil <filippoma... (AT) gmail (DOT) com> wrote:
Created a table and copy-pasted your SQL .. Worked after correcting
a typo in your query -- "surmane"


create table people(id int,name char(10),surname char(10))
DB20000I The SQL command completed successfully.

MERGE INTO people AS t USING (select '5' as id, 'chuck' as name,
'norris' as surname from SYSIBM.SYSDUMMY1)As s ON (t.id = s.id) WHEN
MATCHED THEN UPDATE SET t.name=s.name, t.surname=s.surname WHEN NOT
MATCHED THEN INSERT (id, name, surname) VALUES (s.id, s.name,
s.surname)
DB20000I The SQL command completed successfully.

On 2012-02-15 14:53, phil wrote:

[...]

That is allowed, what error did you get?

Should also work, but you should probably name your columns otherwise

USING VALUES('5','chuck','norris') AS s(id,name,surname)


/Lennart