I have a form that takes in some fields. I want these fields to be inserted into a table if its a new record, if the records exists, then I want the record to be updated. I believe that I should use merge for this. Can someone show me what it should look like. The code below is what I am using now.
SELECT COUNT (*)
INTO my_ip_num
FROM RETENTION
WHERE participant_id = participant_id1;
IF my_ip_num < 1
THEN
INSERT INTO RETENTION
(participant_id, opting_out
)
VALUES (participant_id1, '1'
);
ELSE
UPDATE RETENTION
SET opting_out = '1'
WHERE participant_id = participant_id1;
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Your form should insert data in a temporary table with the same structure as the destination.
CREATE TABLE retention_temp AS SELECT * FROM retention WHERE 1=0;
Create a trigger after insert containing the following merge command :
MERGE INTO retention r
USING retention_temp t
ON (r.participant_id=t.partic
WHEN MATCHED THEN
UPDATE SET
r.opting_out=t.opting_out
WHEN NOT MATCHED THEN
INSERT
(participant_id, opting_out)
VALUES
(t.participant_id, t.opting_out);
If the data isn't in the database yet, but just on the form, you should just process it as you were. Merge is for data that is already 'in' the database, though this may often be an external table, or a pipelined function... If it is just form variables, you can't use merge. Inserting it into a temporary table so that you can use the merge isn't going to help your performance... it will just be extra work, all the inserts, then all the inserts and updates.
My problem with how I have the code originally setup is that if someone submits the form twice and decides to leave a field blank that they didnt leave blank the first time, it sets that collumn to null. How can I avoid this issue. Also, are you saying that the merge will not work with new data coming in from a form?
Someone please clarify for me how I should handle this issue
Merge is for data that is already available to the RDBMS directly... either already in a table, or in something that 'appears' as a table to the RDBMS, like a pipelined function cast to a table, or from an external table, or a real table. If you are coming from something external, then it won't work... at least not to my knowledge - I learn new things too.
As far as application logic, you would need to put checks in if you want to avoid this, and raise a message, or whatever is appropriate.
I used the following code to try to merge the data from the web form to my table. It did not work. So either there is something wrong with my code, or merge is not to be used with this scenario. If I cant use merge to do what I want, then what would you guys suggest that I do?
MERGE INTO RETENTION
USING (
SELECT participant_id
FROM RETENTION
WHERE participant_id = participant_id1
)
ON (participant_id = participant_id1)
WHEN MATCHED THEN
UPDATE SET opting_out = '1'
WHEN NOT MATCHED THEN
INSERT (participant_id, opting_out )
VALUES (participant_id1, '1' );
I think what you are using is correct. As I mentioned to begin with, if the data isn't already on the server, Merge doesn't work. You need to use insert/update and pass the data into the command. Depending on the granularity of the control you have interacting with Oracle, you may be able to do bulk inserts, but if it is only a few records at a time, I think what you have is fine.
Business Accounts
Answer for Membership
by: angelIIIPosted on 2006-03-06 at 07:02:02ID: 16114811
http://www.psoug.org/refer
MERGE
INTO RETENTION d
USING (
SELECT participant_id, COUNT (*) as counter
FROM RETENTION
WHERE participant_id = participant_id1;
) q
ON a.participant_id = q.participant_id
WHEN MATCHED THEN
UPDATE SET opting_out = '1'
WHERE participant_id = q.participant_id;
WHEN NOT MATCHED THEN
INSERT (participant_id, opting_out )
VALUES (q.participant_id, '1' );