lexus lexus - 5 months ago 10
SQL Question

MySQL: Update values for multiple rows under the same column

So I have this table wherein it has columns named AMEX and CashOnly. Both of them have values of either 1 or 0. Now, instead of 1 and 0, I want to change all the '1' values to 'YES' and all the '0' values to 'NO' so it would be much more understandable to the viewer.

What is the most efficient SQL query I could use to accomplish this?

Answer

Use a single UPDATE statement:

UPDATE yourTable
SET AMEX     = CASE WHEN AMEX = '0'     THEN 'NO' ELSE 'YES' END,
    CashOnly = CASE WHEN CashOnly = '0' THEN 'NO' ELSE 'YES' END

Note that this assumes that you have converted your AMEX and CashOnly columns to VARCHAR. Also note that there is no WHERE clause because you want to update all records in your table.

Comments