Insert or update in SQL

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 in开发者_如何学编程stead 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.

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.

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.

Not sure how your database supports Upsert.

-- 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
    ;