John Cobby John Cobby - 4 months ago 14
SQL Question

SQL Subquery to replace all values

I have a query which returns a bunch of different data, however I want to have it replace all the values upon a certain condition.

What I have written below kind of gives me the result I want but not really. It creates a new column instead of replacing the other one:


SELECT
CASE
WHEN T4.[U_DestType] = '6'
THEN (SELECT
'Company Limited' AS [ShipToCode]
)
END AS [ShipToCode],
T2.[ShipToCode],
T6.[StreetS],
T6.[StreetNoS],
T6.[CityS],
T6.[ZipCodeS],
T6.[CountryS],
T5.[LicTradNum],
T2.[CardCode],
T4.[Phone1],
T4.[E_Mail],
T4.[U_DestType],
CASE
WHEN T4.[Country] = 'GB'
THEN 'EN'
ELSE T4.[Country]
END AS [Country],
T4.[U_ShortName]
FROM[...]


The end goal is to replace all of the columns with some preset values instead of just ShipToCode as above.

I tried putting an EXIST subquery after FROM too but that didn't work either.

Is this possible? I'm probably missing something very obvious.

Many thanks!

Answer

You can use an ELSE in your CASE expression to combine the two "columns":

CASE
    WHEN T4.[U_DestType] = '6'
        THEN (SELECT
          'Company Limited' AS [ShipToCode]
          )
    ELSE T2.[ShipToCode]
END AS [ShipToCode],

And by the way, you didn't need to use a Sub-Select. This would work just as well and is easier to read:

CASE
    WHEN T4.[U_DestType] = '6' THEN 'Company Limited'
    ELSE T2.[ShipToCode]
END AS [ShipToCode],