Switching values in a column with one update statement

update

You find that an error in a system has been incorrectly naming men (M) as women (W) and vice versa in the database. The columns only allows for one character. Without using any temp tables, write one update query to resolve this.

This question was asked at a recent interview I had, and I'm going into more interviews that may have similar questions so I wanted to get an idea of how to handle this.

Best Answer

You want to use a CASE expression of some type.

In SQL Server the code would look like this:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W' 
                  WHEN gender = 'W' THEN 'M'
                  ELSE gender END

Edit: As stated in the comments (and some of the other answers) the ELSE isn't necessary if you put a WHERE clause on the statement.

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W' 
                  WHEN gender = 'W' THEN 'M' END
WHERE gender IN ('M','W')

This avoids unnecessary updates. The important thing in either case is to remember that there are options other than M & W (NULL for example) and you don't want to put in mistaken information. For example:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W' 
                  ELSE 'M' END

This would replace any NULLs (or other possible genders) as 'M' which would be incorrect.

A couple of other options would be

/*Simple form of CASE rather than Searched form*/
UPDATE TableName
SET    gender = CASE gender
                  WHEN 'M' THEN 'W'
                  WHEN 'W' THEN 'M'
                END
WHERE  gender IN ( 'M', 'W' );

And a more concise

/*For SQL Server 2012+*/
UPDATE TableName
SET    gender = IIF(gender = 'M', 'W', 'M')
WHERE  gender IN ( 'M', 'W' );

Related Solutions

Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement

UPDATE
   T1
SET
   position = T2.rn
FROM
   myTable T1
   JOIN
   (
   SELECT
      id,
      ROW_NUMBER() OVER (ORDER BY position) AS rn
   FROM
      myTable
   ) T2 ON T1.id = T2.id

Note: the order of "Keyboard" and "USB cable" is arbitrary. They both have position = 0

To tie-break positions based on item, add a secondary sort

      ROW_NUMBER() OVER (ORDER BY position, item) AS rn

If you have duplicate position,item pairs this will be arbitrary too...

Sql-server – Help with tricky update statement

The performance impact from multiple logical reads could be a result of your UDF.

Provided is a great article which describes using inline table-valued UDFs to reduce the row-by-row calls to the scalar UDF. UPDATE: I noticed you were already using table-valued UDF.

http://dataeducation.com/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post/

To replicate your scenario, I executed the following query:

DECLARE
   @Measurements TABLE(Precidence int, -- the relative scale of a measurement
             Measurement varchar(max), -- Grams, Kilograms, Liters, etc
             MeasurementType varchar(max)); -- Weight, Volume, etc`

DECLARE
   @Items TABLE(ItemType varchar(max),   
            Quantity float,  
            Measurement varchar(max),   
            ToMeasurement varchar(max));

insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','G');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','KG');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','ML');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','L'); 

insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','G','Weight');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','KG','Weight');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','GAL','Volume');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','L','Volume');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('3','ML','Volume');

Afterwards, I complied and executed the following update statement:

WITH Items_CTE AS
   (
        SELECT A.ItemType, 
               A.Quantity,
               A.Measurement,  
               (SELECT TOP 1 M.Measurement FROM @Measurements M
                JOIN @Items C ON C.Measurement = M.Measurement
                WHERE M.MeasurementType = B.MeasurementType
                AND  C.ItemType = A.ItemType
                 ORDER BY Precidence)ToMeasurement
       FROM @Items A
       JOIN @Measurements B
       ON A.Measurement = B.Measurement
    )  
UPDATE @Items  
SET ToMeasurement = cte.ToMeasurement  
FROM ITEMS_CTE as cte
JOIN @Items as i
ON cte.measurement = i.measurement;

The UPDATE statement uses a CTE and the results were as follows:

ItemType | Quantity | Measurement | ToMeasurement  
Widget   |    1     |    G        |    KG  
Widget   |    1     |    KG       |    KG  
Widget   |    1     |    ML       |    L  
Widget   |    1     |    L        |    L

Best Answer

Related Solutions

Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement

Sql-server – Help with tricky update statement

Related Question