Sql

Check if a record exists and update it, or insert record if it does NOT exist

Scenario: You have a web page that updates database values, and you need to know whether to do an insert for a new record, or an update for an existing record. You query the database for the record and if it exists then update it, or insert the record if the query returns no results. However this requires 2 calls to the database and if someone else created the record immediatley after you queried it(not likely but could happen) your script would think the record does not exist and attempt to creat a new record, which would result in an error. The following SQL statement will check for the existance of record, update it if it exists, or insert it if it does not exist.

Table Name: employees

Key f_name l_name some_data
1 Jack Ass He is a jackass
2 Mike Hunt What a guy!
3 Dumb Ass He is a dumb ass
IF EXISTS
    (
        SELECT employees.Key
        FROM employees
        WHERE Key = 3
    )
    UPDATE employees
        SET some_data = 'He is not a jackass'
        WHERE employees.Key = 3
ELSE
    INSERT INTO employees(Key, f_name, l_name)
    VALUES(3, Jack, Ass)

Break down:

This line executes a query contained within the trailing (). If that query returns true it executes the SQL command directly after the closing ). If it returns False, then it will execute the SQL commands after the ELSE.

IF EXISTS

Query the employees table and see if there is a record with the Key value set to 3

SELECT employees.Key
FROM employees
WHERE Key = 3

If the IF statement returned true we will update the record. This command updates the some_data field within the employees table, but only updates the record where they Key = 3. If you leave the where clause out of this statement it will update every some_data record in the table.

UPDATE employees
        SET some_data = 'He is not a jackass'
        WHERE employees.Key = 3

If the IF statement returns false, do this

ELSE

Insert the following fields into a new record within the employees table: Key, f_name, l_name. and set their values to: 3, Jack, Ass respectively

INSERT INTO employees(Key, f_name, l_name)
VALUES(3, Jack, Ass)
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License