Wednesday, December 3, 2008

Insert or update a record if it already exists?

SQL 92 dialect question:

Insert or update a record if it already exists?

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote

ANSWER(S):

#2 (permalink)
Old 04-17-2006, 07:09 AM
Dimitar
Posts: n/a
Default SQL 92 answer. Re: Insert or update a record if it already exists?
I am afraid there is no sql92 answer to this question.
For sq192 an approach would be to insert every row and use grouping functions for reporting.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
#3 (permalink)
Old 04-17-2006, 07:54 AM
ben ben is offline
Administrator
Join Date: Mar 2007
Posts: 93
ben has disabled reputation
Default MySQL answer. Re: Insert or update a record if it already exists?
MySQL has a special construct for this. Assume the 'username' column below is UNIQUE:

INSERT INTO users (username, email) VALUES ('Jo', 'jo@email.com')
ON DUPLICATE KEY UPDATE email = 'jo@email.com'

The 'ON DUPLICATE KEY' statement only works on PRIMARY KEY and UNIQUE columns.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
#4 (permalink)
Old 05-08-2006, 12:20 AM
Rob137
Posts: n/a
Default T-SQL answer. Re: Insert or update a record if it already exists?
How about this:
IF (EXISTS (SELECT * FROM AA_TestTable AS t1
WHERE t1.ord_num = 'FFF'))
begin
UPDATE AA_TestTable
SET ord_qty = 999
WHERE ord_num = 'FFF'
end
else
begin
INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty)
VALUES('GGG', 'XYZ', 567)
end
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
#5 (permalink)
Old 09-18-2006, 07:35 AM
srinivas
Posts: n/a
Default Oracle answer. Re: Insert or update a record if it already exists?
merge INTO users U1
USING (U1.username = 'Jo' )
when matched then
UPDATE SET U1.email = 'jo@email.com'
when NOT matched then
INSERT(U1.email, U1.username)
VALUES('Jo', jo@email.com);
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
#6 (permalink)
Old 12-12-2006, 12:44 AM
Evil Overlord
Posts: n/a
Default ANY answer. Re: Insert or update a record if it already exists?
INSERT INTO users (username)
SELECT 'Jo'
WHERE 'Jo'
NOT IN (SELECT username FROM users)

UPDATE users
SET email = 'jo@email.com'
WHERE username = 'Jo'

I use this method a lot. Obviously, 'Jo' would usually either be a variable or a field selected from another table.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
#7 (permalink)
Old 05-07-2007, 05:19 PM
Unregistered
Posts: n/a
Default ANY answer. Re: Insert or update a record if it already exists?
Quote:
Originally Posted by Rob137 View Post
How about this:
IF (EXISTS (SELECT * FROM AA_TestTable AS t1
WHERE t1.ord_num = 'FFF'))
begin
UPDATE AA_TestTable
SET ord_qty = 999
WHERE ord_num = 'FFF'
end
else
begin
INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty)
VALUES('GGG', 'XYZ', 567)
end
This opption worked wonders! Thanks for posting
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
#8 (permalink)
Old 05-30-2007, 07:57 AM
Unregistered
Posts: n/a
Default ANY answer. Re: Insert or update a record if it already exists?
Quote:
Originally Posted by Rob137 View Post
How about this:
IF (EXISTS (SELECT * FROM AA_TestTable AS t1
WHERE t1.ord_num = 'FFF'))
begin
UPDATE AA_TestTable
SET ord_qty = 999
WHERE ord_num = 'FFF'
end
else
begin
INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty)
VALUES('GGG', 'XYZ', 567)
end
(I assume there is a typo in the last statement, where ord_num should be set to 'FFF').

You should be VERY CAREFUL with things like this. If you can't afford to set the transaction isolation level to SERIALIZABLE, some other transaction could add the row with ord_num='FFF' after you've tested for its existence, but before you've inserted it. This way you end up with violated primary constraint and error in one of these transactions.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
#9 (permalink)
Old 10-03-2007, 07:22 PM
mplusch
Posts: n/a
Default ANY answer. Re: Insert or update a record if it already exists?
This is a variation that works for tables with multiple primary keys.
If you have a users table with columns of username, dept, and age, and primary keys of username and dept, then this will only insert a user if it doesn't exist already.

INSERT INTO users (username, dept, age) SELECT username='mp', dept='tax', age=5 WHERE (SELECT COUNT (*) FROM users WHERE username='mp' AND dept='tax')=0;

I initially found this syntax confusing, but here's how I 'parsed' it.
This part returns 0 if it doesn't exist:
SELECT COUNT (*) FROM users WHERE username='mp' AND dept='tax'
This part creates a record-like row with static values and represents the record to insert:
SELECT username='mp', dept='tax', age=5
The WHERE-clause following the above part will only return the value to insert if the count is 0.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)