CODEWITHSUNDEEP
mysqlsql
lexus
lexus

Reputation: 101

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?

Upvotes: 1

Views: 1038

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

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.

Upvotes: 1

J. Zend
J. Zend

Reputation: 98

try this :

update table set amex=(case when AMEX = 1 then 'YES' else 'NO' end) ,
CashOnly=(case when CashOnly= 1 then 'YES' else 'NO' end);

OR

update table set amex='Yes' where amex=1;
update table set amex='No' where amex=0;
update table set CashOnly='Yes' where CashOnly=1;
update table set CashOnly='No' where CashOnly=0;

Upvotes: 0

Asif Rahaman
Asif Rahaman

Reputation: 775

If you want to change your column type then you need to do following: (As int field can't store yes/no as value)

create 2 extra enum field name them whatever you want for now. let them be amex1 and cash1. declare their values as 'yes', 'no' set default value as no.

Now execute following 2 queries one by one.

UPDATE table_name SET amex1 = 'yes' WHERE amex = 1;
UPDATE table_name SET cash1 = 'yes' WHERE cash = 1;

Now delete cash and amex column rename cash1 and amex1 to cash and amex respectively. hope this helps you.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269523

Just use a case statement:

select (case when AMEX = 1 then 'YES' else 'NO' end) as AMEX,
       (case when cash = 1 then 'YES' else 'NO' end) as cash
. . .

Upvotes: 0

Related Questions