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

I think [Records Ordered by rowID] could be part of a view which may not be updatable (includes deletion).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.