MYSQL - REPLACE() function to entire string instead of part of it-CodePudding

In column 1 I have 4 values

N
No
Y
Yes

I tried to replace N with No and Y with Yes with the following code

UPDATE nashupdated
SET SoldAsVacant = REPLACE(SoldAsVacant, "N", "No"),
    SoldAsVacant = REPLACE(SoldAsVacant, "Y", "Yes");

However, it also replaces the "Y" in "Yes" for example, becoming "Yeses"

How should I write the function so that it looks for the entire string only?

CodePudding user response：

Using an IF you could do it like this

UPDATE nashupdated
SET SoldAsVacant = IF(SoldAsVacant = 'N', 'No', SoldAsVacant ),
    SoldAsVacant = IF(SoldAsVacant = 'Y', 'Yes', SoldAsVacant );

This tests the existing value of the column and if it matches sets it to the new value and if not sets it to the existing value.

CodePudding user response：

UPDATE nashupdated
SET SoldAsVacant = REPLACE(SoldAsVacant, "N", "No"),
    SoldAsVacant = REPLACE(SoldAsVacant, "Y", "Yes")
WHERE SoldAsVacant IN ('Y', 'N');

This is more useful than IF/CASE, especially if SoldAsVacant is indexed.

CodePudding user response：

Easy Way

UPDATE nashupdated set SoldAsVacant ='Yes' Where SoldAsVacant ='Y';
 
UPDATE nashupdated set SoldAsVacant ='No'  Where SoldAsVacant ='N';

CodePudding user response：

Use a CASE expression:

UPDATE nashupdated
SET SoldAsVacant = CASE SoldAsVacant 
                     WHEN 'N' THEN 'No'
                     WHEN 'Y' THEN 'Yes'
                   END
WHERE SoldAsVacant IN ('Y', 'N');

Or:

UPDATE nashupdated
SET SoldAsVacant = CASE WHEN SoldAsVacant = 'N' THEN 'No' ELSE 'Yes' END
WHERE SoldAsVacant IN ('Y', 'N');

CodePudding user response：

A fun way uses a derived table to lookup the values:

update nashupdated n join
       (select 'N' as old_value, 'No' as new_value union all
        select 'Y' as old_value, 'Yes' as new_value
       ) x
       on n.SoldAsVacant = x.old_value
    set n.SoldAsVacant = x.new_value;

What is nice about this approach is that the join filters down to only the rows that need to be updated and the values are mentioned only once.