CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008linked-server
Eton B.
Eton B.

Reputation: 6291

Problem trying to fix SQL query to return a single result

I'm trying to use this query to delete the rows that are already on a linked server's Database:

GO
USE TAMSTest
GO
DELETE from [dbo].[Hour]
 WHERE [dbo].[Hour].[InHour] = (SELECT [InHour] 
                                  FROM [TDG-MBL-005].[TAMSTEST].[dbo].[Hour])
GO

When there is only 1 row in the linked server's table, SELECT [InHour] FROM [TDG-MBL-005].[TAMSTEST].[dbo].[Hour] returns that single row and the DELETE works as expected. However, with multiple rows in the linked sever's table, it doesn't work since that part of the query returns mutiple rows as its result. How can I work around this?

If there is further information needed please ask, I need to get this done ASAP.

Thanks in advance, Eton B.

Upvotes: 0

Views: 139

Answers (1)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32700

Change your equal sign to an IN statement

DELETE from [dbo].[Hour]
 WHERE [dbo].[Hour].[InHour] IN (SELECT [InHour] 
    FROM [TDG-MBL-005].[TAMSTEST].[dbo].[Hour])

The IN clause allows you to have multiple values in your WHERE clause, and can be used in subqueries as well. Here's more information.

Upvotes: 7

Related Questions