CODEWITHSUNDEEP
sql-serveralias
Naigel
Naigel

Reputation: 9644

Delete from aliased table doesn't work (but select does)

I need to delete some rows from a table which are not available in another similar table. I can correctly select the rows to be deleted using this piece of code

SELECT *
FROM [DB01].[forward].[forward_value] as DB01
WHERE Id_forward like 'test' and
NOT EXISTS (SELECT *
    FROM [DB02].[db_marketdata].[forward].[forward_value] as DB02
    WHERE DB01.Id_Forward = DB02.Id_Forward and
        DB01.Id_Block = DB02.Id_Block and
        DB01.Id_PriceType = DB02.Id_PriceType and
)

When I change SELECT (*) to DELETE I get an error because I can't alias the first table.

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'as'.

How can I delete those values without using an alias to correctly select the rows?

Upvotes: 2

Views: 70

Answers (2)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Okay, you can use alias like this

DELETE DB01
FROM [DB01].[forward].[forward_value] AS DB01
WHERE Id_forward LIKE 'test'
    AND NOT EXISTS (
        SELECT *
        FROM [DB02].[db_marketdata].[forward].[forward_value] AS DB02
        WHERE DB01.Id_Forward = DB02.Id_Forward
            AND DB01.Id_Block = DB02.Id_Block
            AND DB01.Id_PriceType = DB02.Id_PriceType
            AND
        )

Upvotes: 4

Iswanto San
Iswanto San

Reputation: 18569

Put your alias in DELETE syntax like this:

delete DB01
FROM [DB01].[forward].[forward_value] as DB01
WHERE Id_forward like 'test' and
NOT EXISTS (SELECT *
    FROM [DB02].[db_marketdata].[forward].[forward_value] as DB02
    WHERE DB01.Id_Forward = DB02.Id_Forward and
        DB01.Id_Block = DB02.Id_Block and
        DB01.Id_PriceType = DB02.Id_PriceType 
)

Upvotes: 2

Related Questions