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?
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.
SET statement in there. It should work now.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
. . .
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.
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;
AMEXandCashOnlycolumns?