2

Imagine a table that maps from a string to two integers. I would like to either insert a new row if the key is not in the table, or update the existing row by adding up the integers (key, oldx+x, oldy+y). I'll be doing this a lot and I wonder if I can do this in one operation instead of first SELECT to check if the row exists and then INSERT or UPDATE with the sum as this probably will result in two lookups.

I'm new to SQL and I can't figure how to do this in an efficient way.

Giovanni Funchal
  • 8,934
  • 13
  • 61
  • 110
  • Which DBMS are you using? PostgreSQL, Oracle, Firebird, DB2, SQL Server, MySQL? –  Sep 17 '11 at 09:06

4 Answers4

4

SQLite supports INSERT OR REPLACE, which needs to be used with more care than most people think. See this SO answer for details, and keep in mind the possibility of cascading deletes during replacement.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
2

Doing both will work:

UPDATE TABLE SET x=x+?, y=y+? WHERE key = ?; -- will do nothing if key not found

INSERT INTO TABLE (key, x, y)
SELECT ?, ?, ?
WHERE NOT EXISTS (SELECT * FROM TABLE WHERE key = ?); -- will do nothing if key found

Only one will ever affect the data in your table.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
1

Not sure how your database supports Upsert.

amit kumar
  • 20,438
  • 23
  • 90
  • 126
1
-- Insert record with key=k if it does not exist yet.
-- the zero's could also be handled by appropiate defaults for oldx,oldy 
INSERT INTO tab (key, oldx, oldy) select k,0,0 
    WHERE NOT EXISTS (SELECT 1 FROM tab where key=k)
    ;

UPDATE tab
    SET oldx=oldx+y, oldy=oldy+y
    WHERE key = k
    ;
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • This isn't working for me. the SELECT WHERE NOT EXISTS is returning a row for each row in the database not matching the final WHERE clause... as a result, it is inserting the new record multiple times. – Michael Jan 28 '13 at 19:10