hi all,

I am trying to run an update operation in sql and encountering the above mentioned error. I am unable to figure out, where I went wrong. can someone please help me out..

here is the SQL script I  am trying to to run

update dbo.FileReport SET dbo.FileReport.Resolved=1
 where dbo.FileReport.FilePath IN 
 (SELECT A.FilePath from dbo.FileReport AS A LEFT OUTER JOIN dbo.FileReport_Latest AS B ON A.FilePath = B.FilePath where B.FilePath IS NULL);

thanks,

Vijay

I believe the problem is with my update  trigger on this table and I  am getting this error in the trigger.  Probably because of the most common coding error in triggers - failing to handle multle rows being deleted/updated/inserted by the same command.     can anyone please suggest me a way out

I don't think that query will cause that issue.  Can you post the actual query you are running?

You normally get this problem when running this type of query:

SELECT FilePath, ( SELECT FilePath FROM dbo.FileReport  )
FROM dbo.FileReport 

You could more easily write your first query with only one copy of dbo.FileReport doing something like this:

UPDATE fr
SET fr.Resolved = 1
FROM dbo.FileReport fr
WHERE NOT EXISTS
	(
	SELECT *
	FROM dbo.FileReport_Latest frl
	WHERE fr.FilePath = frl.FilePath
	)

hi wBob,

It is still throwing the same error, when running your suggested script. I am sure now that is has something to do with the trigger

trigger code:

CREATE TRIGGER dbo.Update_Resolved_Column ON dbo.FileReport
FOR UPDATE
AS
SET NOCOUNT ON
IF ( UPDATE(Resolved))
BEGIN
 DECLARE @newvalue bit
     DECLARE @oldvalue bit
 DECLARE @resolved bit
 SET @newvalue = (SELECT Resolved FROM Inserted)
     SET @oldvalue = (SELECT Resolved FROM Deleted)
     IF @newvalue != @oldvalue
     BEGIN
        SET @resolved = (SELECT Resolved FROM Inserted)
        INSERT INTO dbo.Updated_FileRecords select dbo.FileReport.FilePath, dbo.FileReport.FileUnavailable ,dbo.FileReport.FileSizeinvalid, dbo.FileReport.HashInvalid, dbo.FileReport.LastModifed, dbo.FileReport.Reported, @resolved from dbo.FileReport;
     END
END

same columns for all the three tables dbo.FileReport, dbo.FileReport_Latest and dbo.Updated_FileRecords

CREATE TABLE dbo.Updated_FileRecords
(
               FilePath nvarchar(900) NOT NULL,
               FileUnavailable bit NULL,
               FileSizeinvalid bit NULL,
               HashInvalid bit NULL,
   LastModifed nvarchar(40) NULL,
               Reported nvarchar(23) NULL,
   Resolved bit DEFAULT(0),
   PRIMARY KEY (FilePath) 

) ;

 SET @newvalue = (SELECT Resolved FROM Inserted)
 SET @oldvalue = (SELECT Resolved FROM Deleted)

Hello,

Please remind, that the trigger don't fire per each row, it fires once per transaction. And this means, the virtual tables "inserted" and "deleted" can contaion 1-n rows; your triggers acts like it always would contaion only one row and this causes problems / failures.

thank you Olaf, now I spot the issue..

SELECT * FROM Comments WHERE AdvertisementID IN (SELECT ID FROM AdPost WHERE Title LIKE @Title + '%')

This query allows you to retrieve multiple records using sub query....