MERGE statement
User needed a single SQL statement to insert a row into a table only if it doesn't already exist (col1='abc.123.xyz'). Initial MERGE attempt failed. Solution: use MERGE with a derived table source (SELECT statement or collection) instead of merging against the target table itself. Example: MERGE INTO tab1 USING (SELECT 'abc.123.xyz', 'dummy' FROM sysmaster:sysdual) ON condition WHEN NOT MATCHED THEN INSERT. User confirmed this approach works.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm running on IDS 11.5
with the following table:
create table tab1 (
col1 varchar(15) primary key,
col2 varchar(20) )
Situation:
I want a sql statement which can help me to perform the following:
1. I want to know if the record is exist in tab1 where col1="abc.123.xyz", if
no then perform an insert to it.
Note: I need a single SQL statement to accomplish them.
I had tried the following
MERGE INTO tab1 as a
USING tab1 as b
ON a.col1 = b.col1 and a.col1 = "abc.123.xyz"
WHEN NOT MATCHED THEN insert values ("abc.123.xyz", "dummy")
but it does not work.
Any other SQL statement that enable me to accomplished the above needs?
Many thanks
Several possibilities:
1) If you have a unique index, unique constraint, or primary key constraint
on col1 in that table then just do the insert. It will fail if the row
already exists.
2) You can insert the values into a temp table then use the MERGE statement:
create temp table newrows( col1 varchar(15), col2 varchar(20) );
insert into newrows values ('abc.123.xyz', 'dummy');merge into tab1 as a
using newrows as b
on a.col1 = b.col1
when not matched then
insert values (b.col1, b.col2 );
3) Similar to 2). If you have the data you need to insert in a file, you
may be able to define an external table to map the file into the server and
use that instead of the temp table in 2) above.
Otherwise, you will have to do the existence checking in code using more
than a single SQL command. You could write an SPL stored procedure to do
this for you then your script would just have a single SQL in it to call
the procedure.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Apr 17, 2012 at 6:37 AM, LEY PATRICK <patrickley@gmail.com> wrote:
> I'm running on IDS 11.5
>
> with the following table:
>
> create table tab1 (>
> col1 varchar(15) primary key,
>
> col2 varchar(20) )
>
> Situation:
>
> I want a sql statement which can help me to perform the following:
> 1. I want to know if the record is exist in tab1 where col1="abc.123.xyz",
> if
> no then perform an insert to it.
> Note: I need a single SQL statement to accomplish them.
>
> I had tried the following
> MERGE INTO tab1 as a
> USING tab1 as b
> ON a.col1 = b.col1 and a.col1 = "abc.123.xyz"
> WHEN NOT MATCHED THEN insert values ("abc.123.xyz", "dummy")
>
> but it does not work.
>
> Any other SQL statement that enable me to accomplished the above needs?
>
> Many thanks
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5299a31eab7f504bdddb184
Hi Ark Thanks for the fast feedback. I can manually perform the SQL statement insert into tab1 values ('abc.123.xyz', 'dummy'). But after i deleted above record using (delete from tab1 where col1='abc.123.xyz') and perform the merge statement above, the merge statement failed with error messages (insert duplicate row). I think option b from your previous suggestion should work.... PS: unfortunately, writing SPL and any other if then else statement or script is not possible in this case. Thanks for your valuable input.
What you can do is utilize a collection derived table. See the
example below. the first merge statement inserts data1, but
the second insert statement does not run because the row
already exists. Hope this helps,
create table tab1 ( c1 varchar(15) primary key,c2 varchar (20) );
MERGE
INTO tab1 AS t
USING ( select "abc.123.xyz", "data1" FROM sysmaster:sysdual)
as s(col1,col2)
ON t.c1 = s.col1
WHEN NOT MATCHED THEN INSERT
(t.c1, t.c2)
VALUES
(s.col1, s.col2);
select * from tab1;MERGE
INTO tab1 AS t
USING ( select "abc.123.xyz", "data2" FROM sysmaster:sysdual) as s
(col1,col2)
ON t.c1 = s.col1
WHEN NOT MATCHED THEN INSERT
(t.c1, t.c2)
VALUES
(s.col1, s.col2);
select * from tab1;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 04/17/2012 03:37:34 AM:
> From: "LEY PATRICK" <patrickley@gmail.com>
> To: ids@iiug.org
> Date: 04/17/2012 03:39 AM
> Subject: MERGE statement [26720]
> Sent by: ids-bounces@iiug.org
>
> I'm running on IDS 11.5
>
> with the following table:
>
> create table tab1 (>
> col1 varchar(15) primary key,
>
> col2 varchar(20) )
>
> Situation:
>
> I want a sql statement which can help me to perform the following:
> 1. I want to know if the record is exist in tab1 where
col1="abc.123.xyz", if
> no then perform an insert to it.
> Note: I need a single SQL statement to accomplish them.
>
> I had tried the following
> MERGE INTO tab1 as a
> USING tab1 as b
> ON a.col1 = b.col1 and a.col1 = "abc.123.xyz"
> WHEN NOT MATCHED THEN insert values ("abc.123.xyz", "dummy")
>
> but it does not work.
>
> Any other SQL statement that enable me to accomplished the above needs?
>
> Many thanks
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi John/Art Both solution provided does work well. Thanks. Below are my modified version. merge into tab1 as a using (select '1.1.1.1.1.128' as col1 from systables where tabid = 1) as b on a.c1 = b.col1 when NOT MATCHED THEN insert (c1, c2) values (b.col1, 'bla bla bla' ) ;
Great! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 7, 2012 at 4:00 AM, LEY PATRICK <patrickley@gmail.com> wrote: > Hi John/Art > > Both solution provided does work well. Thanks. Below are my modified > version. > > merge into tab1 as a > using (select '1.1.1.1.1.128' as col1 from systables where tabid = 1) as b > on a.c1 = b.col1 > when NOT MATCHED THEN > insert (c1, c2) > values (b.col1, 'bla bla bla' ) ; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340e21d6c59804bf6f8f5e