MySQL update case help

by 
UPDATE  ae44
SET     price =
        CASE
        WHEN ID = 'AT259793380' THEN
                '500'
        WHEN ID = 'AT271729590' THEN
                '600'
        WHEN ID = 'AT275981020' THEN
                '700'
        END

Note than this query will update prices for the records with other ids to NULL.

If you only want to update only ids from your list, use this:

UPDATE  ae44
JOIN    (
        SELECT  'AT259793380' AS oldval, '500' AS newval
        UNION ALL
        SELECT  'AT271729590' AS oldval, '600' AS newval
        UNION ALL
        SELECT  'AT275981020' AS oldval, '700' AS newval
        ) q
ON      ae44.id = q.oldval
SET     price = q.newval

More Related Contents:

  1. SQL Query needed for fetch value from Two identical table but not fully identical table
  2. MySQL ERROR 1045 (28000): Access denied for user ‘bill’@’localhost’ (using password: YES)
  3. How to reset AUTO_INCREMENT in MySQL
  4. MySQL: Insert record if not exists in table [duplicate]
  5. MySQL Query GROUP BY day / month / year
  6. How do I escape special characters in MySQL?
  7. Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user
  8. SQL counting all rows instead of counting individual rows
  9. MySQL – ERROR 1045 – Access denied
  10. Remote Connections Mysql Ubuntu
  11. MySQL InnoDB not releasing disk space after deleting data rows from table
  12. In MySQL, should I quote numbers or not?
  13. Dump all tables in CSV format using ‘mysqldump’
  14. MySQL add days to a date
  15. How can I loop through all rows of a table? (MySQL)
  16. Select columns across different databases
  17. Check for x consecutive days – given timestamps in database
  18. Export a MySQL Database to SQLite Database
  19. How to display open transactions in MySQL
  20. SQL search multiple values in same field
  21. Removing duplicates with unique index
  22. MySQL – Select the last inserted row easiest way
  23. Getting number of rows inserted for ON DUPLICATE KEY UPDATE multiple insert?
  24. MySQL query, MAX() + GROUP BY
  25. ALTER table – adding AUTOINCREMENT in MySQL
  26. Does MySQL foreign_key_checks affect the entire database?
  27. Strip last two characters of a column in MySQL
  28. MySQL cartesian product between two SELECT statements
  29. mysql double-quoted table names
  30. Speeding up mysql dumps and imports [closed]

Leave a Comment