Question Problems with an SQL Statement using UPDATE TOP 1

[bdinnocenzo]

I have the following SQL statement and I get the error message:

"Syntax error in UPDATE statement"

[FONT="Courier New"]            cmd.CommandText = "UPDATE TOP 1 ShoutOuts " & _
                                "SET Canceled=Yes " & _
                                "WHERE BidderID=@BidderID " & _
                                "AND Description=@Description " & _
                                "AND Amount=@Amount"[/FONT]

The statement works if I remove the "TOP 1" but I may have multiple entries in the table that match the "WHERE" criteria and only want to set "Canceled" to one of them.

Thanks,

-Bill

 

[jmcilhinney]

I haven't checked to confirm it but I'm fairly sure that TOP is only valid in a SELECT statement. You'd have to perform a query first to get the top record, then perform the update on that record. Something like this should do it:

UPDATE ShoutOuts
SET Cancelled = Yes
WHERE ShoutOutID IN
(
    SELECT TOP 1 ShoutOutID
    FROM ShoutOuts
    WHERE BidderID = @BidderID
    AND Description = @Description
    AND Amount = @Amount
)

 

[bdinnocenzo]

That did it! I had to add a primary key ShoutOutID since it is used to select the Top 1, and I also added another WHERE qualifier...WHERE Canceled=No...so if there were multiple entries, like 4 or 5, and you canceled one (set Canceled = Yes), further attempts to cancel more would essentially set the same record's Canceled column to yes.

I spent many hours today searching for a solution and came up empty. Thank you!