Question: I have a SQL query with a case statement. The case statement has a subquery. I want to replace (delete) the subselect with a join. It is possible? How can i do it?

The Query:

```
SELECT tablex.Abnumber, CASE WHEN Abdate is not null AND isnull(Abnumber,0) > 1 AND Abdate < (SELECT Abdate FROM t_tablex WHERE Annumber = @Annumber AND Abnumber= @Abnumber-1) THEN 'bla bla' ELSE '' END
FROM t_tablex AS tablex
WHERE (@Annumber IS NULL OR tablex.Annumber= @Annumber)
  AND (@AbnumberIS NULL OR tablex.Abnumber= @Abnumber)

```

Answer: 
If I understood correctly, you are trying to update ValueX columns based on FieldX data.
In that case you need to write Case condition for every column.

You can directly write 10 Case conditions like below

```
UPDATE A_LOG 
SET VALUE1 = CASE WHEN FIELD1 IN ('AAA','BBB','CCC','DDD','EEE' ) THEN 0 ELSE VALUE1 END,
VALUE2 = CASE WHEN FIELD2 IN ('AAA','BBB','CCC','DDD','EEE' ) THEN 0 ELSE VALUE2 END,
VALUE3 = CASE WHEN FIELD3 IN ('AAA','BBB','CCC','DDD','EEE' ) THEN 0 ELSE VALUE3 END
.
.
VALUE10 = CASE WHEN FIELD10 IN ('AAA','BBB','CCC','DDD','EEE' ) THEN 0 ELSE VALUE10 END

```

Or you can generate Update statement with Dynamic query like below

```
DECLARE @IN VARCHAR(100)=' IN (''AAA'',''BBB'',''CCC'',''DDD'',''EEE'' )'
, @SQL VARCHAR(MAX) =  'UPDATE A_LOG SET '

SELECT @SQL = @SQL+  VAL FROM 
(
SELECT    COLUMN_NAME + ' = CASE WHEN FIELD'+ REPLACE(COLUMN_NAME,'VALUE','')
+ @IN+ ' THEN 0 ELSE '+ COLUMN_NAME + ' END,
' VAL
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'LOG'
AND COLUMN_NAME LIKE 'VALUE%'
)A

SELECT @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-3)
SELECT @SQL

```

That will give you update statement for every column with a case condition.
Try this:

```
    Declare @Table Table(Field1 varchar(10), Value1 varchar(10),  Field2 varchar(10),  Value2 varchar(10) ,Field10 varchar(10) , Value10 varchar(10))
    Declare @NewTable Table(Field1 varchar(10), Value1 varchar(10),  Field2 varchar(10),  Value2 varchar(10) ,Field10 varchar(10) , Value10 varchar(10))

    insert into @Table
    SELECT 'AAA','9A','AAA','9A','CCC','World' Union All
    SELECT 'BBB','1','KKK','0',NULL,NULL    Union All
    SELECT 'ZZZ','9','AAA','9A','CCC','42'   Union All    
    SELECT 'CCC','7',NULL,NULL,NULL,NULL Union All
    SELECT 'LLL','9','AAA','3','III','98' Union All
    SELECT 'KKK','3','AAA','4',NULL,NULL    Union All
    SELECT 'DDD','100','AAA','Hello','CCC','6' 

    INSERT into @NewTable
    Select Field1,CASE WHEN Field1 in ('AAA','BBB','CCC','DDD','EEE') THEN 0 else Value1 end As Value1
        ,Field2,CASE WHEN Field2 in ('AAA','BBB','CCC','DDD','EEE') THEN 0 else Value2 end As Value2
        ,Field10,CASE WHEN Field10 in ('AAA','BBB','CCC','DDD','EEE') THEN 0 else Value10 end As Value10
    from @Table 

    Select * from @NewTable

```