Looking for an alternative to cursor

Question

I'm trying to figure out a better and more efficient way to write the script below. Can anyone think of a way to accomplish the same goal without using a cursor?

The "User" may appear multiple times in table1 one but can only exist once in table2.

TABLE1

|Name   |Access   |
-------------------
User1   |N        |
User1   |N        |
User1   |Y        |

TABLE2

|Name   |Access   |
-------------------
User1   |         |
User2   |         |
User3   |         |

Code:

DECLARE @Name VarChar(50), @Access VarChar(1)

DECLARE TestCursor CURSOR FOR 
    SELECT Name, Access FROM Table1 ORDER BY Obj ASC

OPEN TestCursor
FETCH NEXT FROM TestCursor INTO @Name, @Access

WHILE @@FETCH_STATUS = 0 
BEGIN 
    UPDATE table2 
    SET Table2.Access = CASE 
                            WHEN Table1.Access = 'Y' THEN Table1.Access
                        ELSE Table2.Access END
    FROM table1 
    JOIN table2 ON table1.name = table2.name

    FETCH NEXT FROM TestCursor INTO @Name, @Access
END 

CLOSE TestCursor 
DEALLOCATE TestCursor

does table 1 really list user1 3 times? if so what access should user1 have in the end? and prior answers exist for questions like this... Such as: http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match No need to re-invent the wheel. — xQbert, Jul 02 '15 at 20:29

score 1 · Answer 1 · answered Jul 02 '15 at 20:45

UPDATE  t2
SET     access = t1.access
FROM    (
        SELECT  name, MAX(CASE access WHEN 'Y' THEN 'Y' END) access
        FROM    table1
        GROUP BY
                name
        ) t1
JOIN    table2 t2
ON      t2.name = t1.name
        AND t1.access = 'Y'
WHERE   EXISTS
        (
        SELECT  t1.access
        EXCEPT
        SELECT  t2.access
        )

score 1 · Answer 2 · answered Jul 02 '15 at 20:48

What I'm understanding is that you want to update Table2's access column only if the latest(as defined by the max obj column) access from table1 is "Y".

Try this out:

UPDATE @Table2
SET Access = CA.Access
FROM @Table2 AS T2
CROSS APPLY (
                SELECT TOP 1 Access 
                FROM @Table1 AS T1
                WHERE       T1.Name = T2.Name
                        AND T1.Access = 'Y'
                ORDER BY Obj DESC
            ) CA

Looking for an alternative to cursor

2 Answers2