Update or Insert with where clause

Question

I have a table structure as follows:

Id(PK AI) | DataField1(Varchar) | DataField2(Varchar) | User_Id(int)
1         | abc                 | xyz                 | 12
2         | sdj                 | mnu                 | 09
3         | yjf                 | pku                 | 17
4         | anx                 | vby                 | 11

I want to insert or update this table.

Let's say I want to insert data as DataField1='wer', DataField2='try' where User_Id = 11, provided that a record with given User_Id does not exist. If a record with User_Id=11 exists then update the record.

I want a single query and not multiple queries.

Please keep in mind that User_Id is not unique. Id is Primary key with Auto increment so I'm not touching that field.

I want something which is similar to 'on duplicate key update'. To my knowledge, this query has no 'where condition'.

You can't do this in a single query if you don't have a unique key on `User_Id`. The only way to do both insert and update in a single query is with `ON DUPLICATE KEY UPDATE`, and that requires a unique key. — Barmar, Dec 20 '16 at 09:49
@Barmar, my guess is that `User_Id` column is FK column and thus would be having a unique secondary index on it and so it's fine. — Rahul, Dec 20 '16 at 10:41
@Rahul The question says "`User_Id` is not unique." And a FK doesn't have to be unique. If you have a many-to-many relationship, FK is not unique. — Barmar, Dec 20 '16 at 10:45

score 2 · Accepted Answer · answered Dec 20 '16 at 09:55

I know you said you want a single query, but that's not possible if the User_Id column isn't unique. You can do it with the following two queries:

UPDATE yourTable
SET DataField1='wer', DataField2='try'
WHERE User_Id = 11;

INSERT INTO yourTable (DataField1, DataField2, User_Id)
SELECT 'wer', 'try', 11
FROM dual
WHERE NOT EXISTS (SELECT * FROM yourTable WHERE User_Id = 11)

The WHERE clause in the second query makes the SELECT query return an empty result when the user ID already exists in the table.

Mansoor · Answer 2 · 2018-08-04T04:54:04.010

0

Try below code for update and insert If Id exists : 

IF EXISTS(SELECT 1 FROM yourTable WHERE User_Id = @User_Id)
BEGIN
     UPDATE yourTable SET DataField1='wer', DataField2='try'
     WHERE User_Id = @User_Id 
END
ELSE
BEGIN
   INSERT INTO yourTable (DataField1, DataField2, User_Id)
   SELECT  'wer', 'try', @User_Id
END

IN MYSQL :

IF EXISTS(SELECT 1 FROM wp_options WHERE option_name = 
        'password_protected_version') 
THEN 
     UPDATE wp_options SET option_value='2.2.2' WHERE option_name = 
     'password_protected_version'; 
ELSE 
     INSERT INTO wp_options (option_value, option_name) SELECT '2.2.2', 
     'password_protected_version'; 
END IF;

edited Aug 04 '18 at 04:54

answered Dec 20 '16 at 10:45

Mansoor

4,061
1
17
27

MySQL objected to "IF EXISTS" with the following error: "Unrecognized statement type. (near "IF EXISTS" at position 0)" I don't have time to dig into it much, but guessing it's related to this: https://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists – Sarah Lewis Aug 01 '18 at 15:20
@SarahLewis,Please post your sql query. – Mansoor Aug 02 '18 at 06:16
Here's the SQL: IF EXISTS(SELECT 1 FROM wp_options WHERE option_name = 'password_protected_version') BEGIN UPDATE wp_options SET option_value='2.2.2' WHERE option_name = 'password_protected_version' END ELSE BEGIN INSERT INTO wp_options (option_value, option_name) SELECT '2.2.2', 'password_protected_version' END – Sarah Lewis Aug 03 '18 at 16:15
@SarahLewis, I updated my answer.Check that answer for mysql. – Mansoor Aug 04 '18 at 04:54

Update or Insert with where clause

2 Answers2