Is it possible to write an UPDATE that will update the id values?

Question

SQL

There is a query like this:
SELECT SCHED.mList_id, ml.id, MD.mList_id, SM.mList_id
FROM [TEST].[dbo].[PATP_Schedule] AS SCHED
INNER JOIN TEST..PATP_MarshrutList AS ML
ON SCHED.mList_id = ML. id
INNER JOIN TEST..PATP_MarshrutData AS MD
ON MD.mList_id = ML.id
INNER JOIN [TEST].[dbo].[PATP_SubMarshrut] AS SM
ON ML.id = SM.mList_id
prints table ID values. Help write an UPDATE that will update them starting at 2000 for example. Thanks in advance.

Answer 1

CTE to help you:

WITH cteName as (
SELECT SCHED.mList_id, ml.id, MD.mList_id, SM.mList_id
FROM [TEST].[dbo].[PATP_Schedule] AS SCHED
INNER JOIN TEST..PATP_MarshrutList AS ML
ON SCHED.mList_id = ML.id
INNER JOIN TEST..PATP_MarshrutData AS MD
ON MD.mList_id = ML.id
INNER JOIN [TEST].[dbo].[PATP_SubMarshrut] AS SM
ON ML.id = SM.mList_id
)

UPDATE cteName set mList_id =  <someValue>;

And yes, you can update only one table. If you need to update all fields in general that are selected there, issue SELECT as VIEW and write an INSTEAD OF INSERT trigger for it.