CODEWITHSUNDEEP
mysqlsql
Francis
Francis

Reputation: 147

Update or Insert with where clause

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'.

Upvotes: 0

Views: 2108

Answers (2)

Mansoor
Mansoor

Reputation: 4192

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;

Upvotes: 0

Barmar
Barmar

Reputation: 781706

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.

Upvotes: 2

Related Questions