CODEWITHSUNDEEP
sqlsql-servert-sql
user2146755
user2146755

Reputation: 85

Looking for an alternative to cursor

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

Upvotes: 1

Views: 63

Answers (2)

Stephan
Stephan

Reputation: 6018

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425341

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
        )

Upvotes: 1

Related Questions