CODEWITHSUNDEEP
sql-servert-sqllinked-server
Abs
Abs

Reputation: 57916

Row cannot be located for updating

I'm trying to delete some rows from an events table using the following.

DECLARE @PRODUCTION_ID int = 13811

DELETE openquery(TEST, 
'SELECT p.id as production_id FROM EVENTS ev 
LEFT JOIN production_seasons ps ON ev.production_season_id = ps.id 
LEFT JOIN productions p ON ps.production_id = p.id')
where production_id = @PRODUCTION_ID

However, the query fails when events are returned i.e. when there is a non empty resultset. If the resultset is empty the query just returns (0 row(s) affected).

The error I get is:

OLE DB provider "MSDASQL" for linked server "TEST" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".

I'm not sure what this means, any help appreciated.

Upvotes: 0

Views: 6678

Answers (1)

Abs
Abs

Reputation: 57916

The above query was changed to this:

DECLARE @TSQL varchar(500)
SELECT  @TSQL = 
'DELETE OPENQUERY(TEST,''SELECT ev.* FROM EVENTS ev 
LEFT JOIN production_seasons ps ON ev.production_season_id = ps.id 
LEFT JOIN productions p ON ps.production_id = p.id WHERE p.id = ' + convert(varchar, @PRODUCTION_ID) + ''')'
EXEC (@TSQL)

The important part is the select ev.* as it seems to make it specific to delete from the events table. This page helped me to realise this.

Since you can't pass variables via openquery I had to make use of one of these.

Upvotes: 2

Related Questions