CODEWITHSUNDEEP
phpmysqlsql-update
Karthik K
Karthik K

Reputation: 35

How to update specific column without duplicate and like replace the value

I need to update a row in my table.
This task like a replace the value of specific column in table.
I'm not using PRIMARY KEY or UNIQUE KEY (Because I can't insert duplicate value in that particular column).

 +-----+-----+-----+
 | id  |col1 |col2 |
 +-----+-----+-----+
 | 1   | a   | 404 |
 +-----+-----+-----+
 | 2   | b   |  22 |
 +-----+-----+-----+

Now I update my table :

   UPDATE table_name SET col2 = 0 WHERE col2 = 404;
   UPDATE table_name SET col2 = 404 WHERE id = 2;       

This result I want. 
 +-----+-----+-----+
 | id  |col1 |col2 |
 +-----+-----+-----+
 | 1   | a   |  0  |
 +-----+-----+-----+
 | 2   | b   | 404 |
 +-----+-----+-----+

I have used two query in order to get it done. Is there any possible function in a single query? (OR) is there any simpler method?

Upvotes: 2

Views: 106

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 77063

As Mureinik has already said, you can use a case expression. It is better to have a single update, especially if you are creating two requests for the two updates currently. However, if the order of the execution is important for some reason, then you cannot put them together like that.

Upvotes: 0

Mureinik
Mureinik

Reputation: 312257

You could use a case expression:

UPDATE table_name 
SET col2 = CASE col2 WHEN 0 THEN 404 WHEN 404 THEN 2 END
WHERE col2 IN (2, 404);

But frankly, using two separate statements seems clearer to me.

Upvotes: 2

Related Questions