0

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
2
Contributors
1
Reply
2
Views
9 Years
Discussion Span
Last Post by cutepinkbunnies
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.