error when deleting with the WITH clause?

harcaype

hi. I'm trying to delete a row by specifying the actual row number. When I call all columns from one table it does work but when I already multiselect them I get an error: Msg 4405, Level 16, State 1, Line 1 View or function 'Records ORDERED BY rowID' is not updatable because the modification affects multiple base tables. This one works:

WITH [Records ORDERED BY rowID] AS
(SELECT row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID,*)
DELETE FROM [Records ORDERED BY rowID] WHERE rowID=1

This one gets the error:

WITH [Records ORDERED BY rowID] AS
(SELECT row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID,
Records.[Officer ID],
DriverInfo.[Plate Number],
DriverInfo.[License Number],
DriverInfo.[Conduction Number],
DriverInfo.[Vehicle Category],
DriverInfo.[Vehicle Type],
DriverInfo.[Vehicle Brand],
DriverInfo.[Last Name],
DriverInfo.[First Name],
DriverInfo.[Middle Name],
DriverInfo.[Birth Date],
DriverInfo.[Registered Address],
DriverInfo.[Address' City Code],
Records.[Violation Commited],
Violations.[Violation Code],
Records.[Street Name],
Records.[City of Apprehension],
Cities.[City Code],
Violations.[Fine Amount],
Records.[Date/Time Apprehended]
FROM Violations,DriverInfo,Records,Cities
WHERE Records.[Plate Number] like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Violations.Violations
and Records.[City of Apprehension] like Cities.City)
DELETE FROM [Records ORDERED BY rowID] WHERE rowID=1

tsaunders

Because you are pulling information from multiple tables, it can not figure out which row number you are using Try changing to this for example: (SELECT Records.[Plate Number], row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID,

harcaype

I see. How can I use this when calling multiple tables then? I need to invoke multiple tables because I only want to delete the ones that satisfy that multiselect query. If I just use one table. The other rows that are not supposed to be included in my deletion will be deleted. Like when I want to delete row 1 in the condition of the multiselect I created, and when I only specify one table, the row 1 that it will be reading is the row 1 of that table not the multiselected table. Got any suggestions? :confused:

leoinfo

Is it this what you are looking for?

WITH ORDERED_RECORDS AS(
SELECT
row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID
,Records.[Officer ID]
,DriverInfo.[Plate Number]
,DriverInfo.[License Number]
,DriverInfo.[Conduction Number]
,DriverInfo.[Vehicle Category]
,DriverInfo.[Vehicle Type]
,DriverInfo.[Vehicle Brand]
,DriverInfo.[Last Name]
,DriverInfo.[First Name]
,DriverInfo.[Middle Name]
,DriverInfo.[Birth Date]
,DriverInfo.[Registered Address]
,DriverInfo.[Address' City Code]
,Records.[Violation Commited]
,Violations.[Violation Code]
,Records.[Street Name]
,Records.[City of Apprehension]
,Cities.[City Code]
,Violations.[Fine Amount]
,Records.[Date/Time Apprehended]
FROM Violations
,DriverInfo
,Records
,Cities
WHERE Records.[Plate Number] like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Violations.Violations
and Records.[City of Apprehension] like Cities.City
)
DELETE FROM Records
WHERE Records.[Plate Number]
IN (
SELECT [Plate Number]
FROM ORDERED_RECORDS
WHERE rowID=1
)

harcaype

Oh gosh. Thanks! I GOT IT WORKING FINALLY!!!! THANK YOU SOOO SOOO MUCH!!!!!! :-D

modified on Thursday, July 17, 2008 12:38 PM