SQL - If Exists Update Else Insert Into

UPDATE if exists else INSERT in SQL

The below query will fulfill your requirement.

INSERT INTO `ALLOWANCE` (`EmployeeID`, `Year`, `Month`, `OverTime`,`Medical`,
`Lunch`, `Bonus`, `Allowance`) values (10000001, 2014, 4, 10.00, 10.00,
10.45, 10.10, 40.55) ON DUPLICATE KEY UPDATE `EmployeeID` = 10000001

SQL - IF EXISTS UPDATE ELSE INSERT Syntax Error

INSERT INTO component_psar (tbl_id, row_nr, col_1, col_2, col_3, col_4, col_5, col_6, unit, add_info, fsar_lock)
VALUES('2', '1', '1', '1', '1', '1', '1', '1', '1', '1', 'N')
ON DUPLICATE KEY UPDATE col_1 = VALUES(col_1), col_2 = VALUES(col_2), col_3 = VALUES(col_3), col_4 = VALUES(col_4), col_5 = VALUES(col_5), col_6 = VALUES(col_6), unit = VALUES(unit), add_info = VALUES(add_info), fsar_lock = VALUES(fsar_lock)

Would work with tbl_id and row_nr having UNIQUE key.

This is the method DocJonas linked to with an example.

SQL - IF EXISTS UPDATE ELSE INSERT INTO

1. Create a UNIQUE constraint on your subs_email column, if one does not already exist:

   ALTER TABLE subs ADD UNIQUE (subs_email)
   

2. Use INSERT ... ON DUPLICATE KEY UPDATE:

   INSERT INTO subs
     (subs_name, subs_email, subs_birthday)
   VALUES
     (?, ?, ?)
   ON DUPLICATE KEY UPDATE
     subs_name     = VALUES(subs_name),
     subs_birthday = VALUES(subs_birthday)
   

You can use the VALUES(col_name) function in the UPDATE clause to
refer to column values from the INSERT portion of the INSERT ... ON
DUPLICATE KEY UPDATE - dev.mysql.com

3. Note that I have used parameter placeholders in the place of string literals, as one really should be using parameterised statements to defend against SQL injection attacks.

Insert into a MySQL table or update if exists

Use INSERT ... ON DUPLICATE KEY UPDATE

QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

If exists UPDATE else INSERT for each row of a table

It's not missing. SQL Server implements the standard MERGE statement which allows you to specify what happens when a match occurs or not between a source and a target. Check the documentation for examples.

Matches are made using a condition that can involve many columns. MERGE allows you to execute an INSERT, UPDATE or DELETE in the following cases:

• A match is found based on the condition
• A match occurs only in the source
• A match occurs only in the target

This way you can update existing rows, insert rows that exist only in the source, delete rows that appear only in the target.

In your case, you could do something like:

MERGE tableTo AS T
USING tableFrom AS S
      ON (T.product= S._product)
WHEN NOT MATCHED BY TARGET
     THEN INSERT(product, something) VALUES(S._product, S.something)
WHEN MATCHED 
     THEN UPDATE SET T.Something= S.Something
OUTPUT $action, Inserted.*, Deleted.*;

This statement will insert or update rows as needed and return the values that were inserted or overwritten with the OUTPUT clause.

---

Related Topics