Need to loop or iterate over a result set

0
votes

I have two columns Col1, Col2 from Table1 in database DB1. Col1 contains data and Col2 is all null. I have a column Col3 which comes from a query on Table2 in another database DB2. I want to set Col2 based on the following condition -

If a row of Col1 EQUAL TO any of the rows in Col3, then set Col2 EQUAL TO row of Col3. ELSE, set it EQUAL TO Col1. Its weird, but I need to do it.

Example -

Col3 is ALWAYS = {Aaa, RTY, Ccc, DART, Car, Fish, SPOON}

Col1,Col2,Col3
Aaa, null, Aaa
Bbb, null, RTY
Ccc, null, Ccc
...

How to make a query which will give us the result below ?

Col1,Col2,Col3
Aaa, Aaa, Aaa
Bbb, Bbb, RTY
Ccc, Ccc, Ccc
...
2
sqlsql-serversql-server-2008

2 Answers

1
votes

Assuming that you want to select rather than update - the most obvious solution to this would be in splitting up the problem into its individual bits:

Replace exy with your Col1, Col2, Col3 view/table/alias.

;WITH exy(Col1, Col2, Col3) AS (SELECT 'Aaa', null, 'Aaa' union SELECT 'Bbb', null, 'RTY' union SELECT 'Ccc', null, 'Ccc')
SELECT a.Col1, a.Col3 AS Col2, a.Col3 FROM exy AS a WHERE EXISTS (SELECT 1 FROM exy WHERE Col3 = a.Col3)
UNION
SELECT a.Col1, a.Col1 AS Col2, a.Col3 FROM exy AS a WHERE NOT EXISTS (SELECT 1 FROM exy WHERE Col3 = a.Col3)

Then you can simplify it down a little:

;WITH exy(Col1, Col2, Col3) AS (SELECT 'Aaa', null, 'Aaa' union SELECT 'Bbb', null, 'RTY' union SELECT 'Ccc', null, 'Ccc')
SELECT a.Col1, CASE WHEN 1 = EXISTS(SELECT 1 FROM exy WHERE Col3=a.Col3) THEN a.Col3 ELSE a.Col1 END AS Col2, a.Col3 FROM exy

There might be a nicer solution but we'd need to see your original tables and understand your problem. This can also be done with joins. But there is often benefit in clarity over performance. When performance is concerned, you will have to know what your targets are before optimising too early.

0
votes

Could this work for your situation?

update t1
set col2 = coalesce(a.col3,a.col1) 
    from    database1.dbo.table1 as t1 inner join (
        select  t1.col1
            ,   t2.col3
        from    database1.dbo.table1 t1 
            left outer join (
                select distinct col3
                from    database2.dbo.table2
                            ) as t2 on t1.col1 = t2.col3
            ) as a on t1.col1 = a.col1

Set col2 to the first non-null value from either col3 or col1 from table 1 left outer joined to the distinct values in table 2, so when there is no match, col3 is null.