mayaldi
asked on
Insert Record
I have a form that was developed using JAVA, and i am updating the DB2 server using type 4 drivers, i have a scenario where i need to check the DB to see if the record exists before inserting, is there a way to do this in one transaction rather than two ( that is first checking to see if it exists, and then inserting ). I know one option would be to set the unique key in the database to prevent multiple records, but unfortunately i cannot do this as there are other forms that allow duplicates to the same table. Any help is appreciated, i am tyring to optimize my code as much as possible....
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Kdo,
Sounds great, but i am not sure how to do this, your help is greatly appreciated ...
mytable structure:
name character(10)
MERGE INTO mytable t0
USING
(
select * from mytable where name = 'mayaldi'
) as t1
on t1.name = t0.name
when not matched then
insert into mytable (name) values('mayaldi')
is this correct ?, i am confused ...
Sounds great, but i am not sure how to do this, your help is greatly appreciated ...
mytable structure:
name character(10)
MERGE INTO mytable t0
USING
(
select * from mytable where name = 'mayaldi'
) as t1
on t1.name = t0.name
when not matched then
insert into mytable (name) values('mayaldi')
is this correct ?, i am confused ...
Now that seems to be a problem.
MERGE is an SQL statement, just like INSERT, UPDATE, etc. 'mytable' and 'name' are program entities, probably from your JAVA source.
The MERGE statement that you have should run just fine. But I'm assuming that you don't want to hard-code 'mayaldi' into the query. You'll need to build the MERGE statement, substituting the program value for 'mayaldi' and you should be just fine.
Kent
Good point Kdo!
ASKER
Kent,
When i run your solution, it comes up with an error, that no rows found to fetch delete or update ? Any ideas
When i run your solution, it comes up with an error, that no rows found to fetch delete or update ? Any ideas
Hi mayaldi,
Its probably because you are using a char data type. In this statement:
select * from mytable where name = 'mayaldi',
'mayaldi' is a varchar data type. So when you compare to the data in the name column, the looks like this:
"mayaldi" = "mayaldi---" (the dashes represent spaces)
You can either change the data type to a varchar so the test works, or trim the values when comparing:
on t1.name = rtrim(t0.name)
Hope that helps
Keith
Its probably because you are using a char data type. In this statement:
select * from mytable where name = 'mayaldi',
'mayaldi' is a varchar data type. So when you compare to the data in the name column, the looks like this:
"mayaldi" = "mayaldi---" (the dashes represent spaces)
You can either change the data type to a varchar so the test works, or trim the values when comparing:
on t1.name = rtrim(t0.name)
Hope that helps
Keith
ASKER
In my actual query they are numeric, and i still get the same result .... Thanks for your help
Start with the basics. :) Run the query portion of the statement. Everything between the '(' and ')'
MERGE INTO mytable t0
USING
(
SELECT * FROM mytable WHERE {condition}
) AS T1
ON T0.f1 = T1.f1
AND T0.f2 = T1.f2
.. etc
WHEN NOT MATCHED THEN
INSERT ....;
If that's NULL, that's the answer. If you get a non-null result set, the issue is in the join.
Kent
ASKER
Yes, the whole idea is that the Select returns null meaning there was nothing found, in which case it should do the insert correct ?
That's correct. If the rest of the query is right.
The MERGE statement effectively does an outer join on base table (that we've renamed t0) and the derived table generated by the select statement (t1).
Can you post your entire MERGE statement? I'll be glad to look at it and tell you what I thin it's doing. (Or not doing....)
Kent
ASKER
Thanks so much kent for your continue help ...
MERGE INTO Product P0
USING
(
SELECT * FROM Product WHERE {ProductCode = '101'}
) AS P1
ON PO.ProductCode = P1.ProductCode
WHEN MATCHED THEN
( UPDATE Product SET ProductDesc = 'Product Desc', ProductCost = 12.99 )
WHEN NOT MATCHED THEN
(INSERT INTO Product (ProductCode, ProductDesc, ProductCost) VALUES('101', 'Product Desc',12.99))
This is the query that the code generates....
Thanks again ...
MERGE INTO Product P0
USING
(
SELECT * FROM Product WHERE {ProductCode = '101'}
) AS P1
ON PO.ProductCode = P1.ProductCode
WHEN MATCHED THEN
( UPDATE Product SET ProductDesc = 'Product Desc', ProductCost = 12.99 )
WHEN NOT MATCHED THEN
(INSERT INTO Product (ProductCode, ProductDesc, ProductCost) VALUES('101', 'Product Desc',12.99))
This is the query that the code generates....
Thanks again ...
The logic looks sound, but the syntax is suspect.
I don't have quick access to a DB2 system right now so I can't test this, but I believe that getting rid of the extra brackets and parentheses will do it.
Try the code below.
Kent
MERGE INTO Product P0
USING
(
SELECT * FROM Product WHERE ProductCode = '101'
) AS P1
ON PO.ProductCode = P1.ProductCode
WHEN MATCHED THEN
UPDATE Product SET ProductDesc = 'Product Desc', ProductCost = 12.99
WHEN NOT MATCHED THEN
INSERT INTO Product (ProductCode, ProductDesc, ProductCost) VALUES('101', 'Product Desc', 12.99)
ASKER
Thanks for your help Kdo, i finially got it ......
If you see the update and insert statements are different when using inside the merge statement, they are not supposed to include the table name .... also the secondary query has to always return a result set for the merge to function.
If you see the update and insert statements are different when using inside the merge statement, they are not supposed to include the table name .... also the secondary query has to always return a result set for the merge to function.
:)
You could use an atomic stored procedure to do this all in one transaction if you wish. I assume if the record already exists, you don't want to insert? Or do you want to update that record? If you want to update, you can try using the MERGE statement.
Keith