Skip to content
Advertisement

Update a Table Variable Where Not Exists

UPDATE 
    @Customer
SET 
    ValidaitonAction = 1
WHERE NOT EXISTS
    (SELECT 1 FROM DMScustomerupload WHERE AccountNumber = @Customer.AccountNumber)

Where @Customer is a TABLE variable:

DECLARE @Customer TABLE ( ID int,
                        ValidaitonAction int,
                        ... other columns))

On the last line, I get

Must declare scalar variable @Customer

Advertisement

Answer

You need a table alias:

UPDATE c
    SET ValidationAction = 1
    FROM @Customer c
    WHERE NOT EXISTS (SELECT 1 FROM DMScustomerupload cu WHERE cu.AccountNumber = c.AccountNumber);

SQL Server automatically interprets anything that starts with a @ as a variable (of some sort). Table aliases are a different thing, so it doesn’t find a match.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement