Prev: Failover problems
Next: SQL0901N Error.
From: rAinDeEr on 15 Feb 2006 23:12
Hi,

I have a table with 2 columns

** CREATE TABLE test (emp_num DECIMAL(7) NOT NULL,emp_name CHAR(10) NOT

NULL) and i have inserted a number of records.

** Now, I want to insert a new record (3232,'Raindeer') based on the
condition that the
emp_num 3232 doesnt exist.

SELECT * ,
CASE
when not exists (SELECT * from test where emp_num=3232)
then insert into test values (3232,'Raindeer')
END
FROM test";

** I get the following error ::
SQL0104N An unexpected token "*" was found following "SELECT ".
Expected tokens may include: "?

** Can anyone help me to modify this code. I would appreciate if some
one would
show me the different variations in acheiving the output.
Thanks in advance.

From: --CELKO-- on 15 Feb 2006 23:29
Look up the MERGE statement in DB2. It is called an "upsert" in the
literature and it is a combination of an UPDATE and INSERT.

From: rAinDeEr on 15 Feb 2006 23:54
I tried MERGE. But it is not working either.

MERGE INTO test A
USING test B
ON A.emp_num =B.emp_num
WHEN MATCHED THEN
UPDATE
SET A.emp_num =B.emp_num
WHEN NOT MATCHED THEN
INSERT
VALUES (3232,'success');

** it showed DB20000I The SQL command completed successfully.

** But when i tried select * from test the record (3232,'success') was
not present

Thanks in advance

From: Liu Liu on 16 Feb 2006 00:19
Why not just create a unique index on the emp_num column? That way, the
insert would fail if a duplicate key was detect. If the condition is
more complex than that, you can always create a check constraint to
stop user from inserting invalid data.

From: rAinDeEr on 16 Feb 2006 00:35
Hi Liu Liu ,

I need the syntax of the

SELECT * ,
CASE
when not exists (SELECT * from test where emp_num=3232)
then insert into test values (3232,'Raindeer')
END
FROM test";

** Or any other statment which will do the same

** that is the requirement
** Thanks all

 |  Next  |  Last
Pages: 1 2 3
Prev: Failover problems
Next: SQL0901N Error.