CODEWITHSUNDEEP
mysqlsqlsql-updatewhere-clause
Matheus Cerqueira
Matheus Cerqueira

Reputation: 77

How to update only if a text value equals and specific text?

I need to update a row only if its param status equals 'CREATED' to 'NEWSTATUS'. I'm trying to do it with the following code, but it's still wrong.

UPDATE table t
SET t.otherId, t.status =
    CASE 
        WHEN (SELECT t.status FROM table t WHERE t.idRow) = 'CREATED' THEN '10', 'NEWSTATUS'
    END
WHERE t.idRow = 23

What should I do?

Upvotes: 0

Views: 80

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you want a CASE expression for the assignment of status:

UPDATE table t
    SET t.otherId,
        t.status = (CASE WHEN t.status = 'CREATED' 
                         THEN '10' ELSE 'NEWSTATUS'
                    END)
    WHERE t.idRow = 23;

Upvotes: 1

GMB
GMB

Reputation: 222582

Are you just looking for...:

UPDATE table SET otherId = 10, status = 'NEWSTATUS' WHERE idRow = 23 AND status = 'CREATED'

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Sounds like you just want a simple UPDATE with a WHERE clause.

UPDATE table
       SET status = 'NEWSTATUS'
       WHERE status = 'CREATED';

Upvotes: 1

Related Questions