Multiple Condition Bulk Update in SQL & PHP

Question

If I want to update a column value base on a condition I can just use the below query for a simpel +1 -1 case (translate into proper PHP call or ORM equivalent, of course):

UPDATE articles SET STATUS = 0 WHERE STATUS  = 1;

But now the issue is that I want, based on the original the status, infer a new value of status from a dictionary. Assuming that the dictionary is something like this:

dict['1']='3'
dict['12']='2'
dict['6']='2'

There is no relationship between key-value pair. The dict is a PHP dictionary object, that's what I meant by "in memory dictionary".

How to write this query?

Edit: Question edit. I didn't frame the question properly... sorry!

Answer 1

Use subquery:

UPDATE articles AR SET status = (SELECT value FROM dict WHERE key = AR.status)

Of course, I assume that you have dictionary stored in your database.

Answer 2

How about something like this:

UPDATE articles
   SET status = n.new
  FROM articles a
  JOIN NewStatus n
    ON a.status = n.status

That way, you'd have a second table called "NewStatus" that had two columns - one called "status" with your existing status, and one called "New" that had the status you wanted it to update to. Join to that table, do your update, and that should solve it. Also, if there are any statuses you don't want to update, just don't include them in your NewStatus table - then they'll be skipped in the update statement.

It looks like the question's been modified, so if I'm missing the point, please let me know!

Answer 3

If you have a small number of mappings then you could use a case statement.

BEGIN TRAN

UPDATE articles
SET status = 
       case 
          when status=1 then 3 
          when status=12 or status=6 then 2 
          else status --leave it unchanged if it doesn't match a case
       end

--COMMIT OR ROLLBACK

Otherwise take the mapping table approach as suggested by rwmnau

Answer 4

Since your question is unclear and you appear to be mixing code and SQL you could do something like this (pseudo Java):

StringBuilder sb = new StringBulder();

for (Map.Entry<Integer,Integer> entry : codes.entrySet) {

    sb.append(
      String.format("UPDATE articles SET Status=%s WHERE Status=%s\n", entry.getValue(), entry.getKey() 
       );

}

String sqlToExecute = sb.toString();
System.out.println(sqlToExecute);

This will give you the SQL to execute to make the changes. If this code is going to be more of a one off then get rid of the String formatting and use prepared statements instead.