Update with select CTE Error

Asked

Viewed 235 times

0

I am having difficulties to perform an update with select CTE. I want to update the charging field. Below is the query and just below is the update I tried to perform, but the following error occurs: "The 'historic' derived table is not updatable because a column of the derived table is derived or constant"

-> QUERY

WITH historico AS (
  SELECT t.taridinclusao AS tarefa_inclusao
    FROM tarefa t
   WHERE t.tarid = 167920 and t.taridinclusao IS NOT NULL
   UNION ALL
  SELECT t.taridinclusao AS tarefa_inclusao
    FROM historico h
         INNER JOIN tarefa t ON t.tarid = h.tarefa_inclusao
   WHERE h.tarefa_inclusao IS NOT NULL and t.taridinclusao IS NOT NULL
)
SELECT top(1)h.*
  FROM historico h
 ORDER BY h.tarefa_inclusao asc
OPTION(MAXRECURSION 0);

-> UPDATE

WITH historico
AS
(SELECT t.taridinclusao AS tarefa_inclusao
    FROM tarefa t
   WHERE t.tarid = 167920 and t.taridinclusao IS NOT NULL
   UNION ALL
  SELECT t.taridinclusao AS tarefa_inclusao
    FROM historico h
         INNER JOIN tarefa t ON t.tarid = h.tarefa_inclusao
   WHERE h.tarefa_inclusao IS NOT NULL and t.taridinclusao IS NOT NULL)
UPDATE historico
SET tarefa_inclusao = tarefa_inclusao
SELECT top(1)h.*
  FROM historico h
 ORDER BY h.tarefa_inclusao asc
OPTION(MAXRECURSION 0);
sql query sql-update sql-server-2012

  • Someone could ?

  • What is the purpose of this query? The way you put it you’re giving update in a table that is only the result of a CTE and exists only in this scope, outside that you are updating a column with itself...

  • Update has not select in its structure

1 answer

1


Do not update the field on CTE and yes in your table tarefa as follows:

WITH historico AS (
  SELECT t.taridinclusao AS tarefa_inclusao,
         t.tarid AS tarefa_referencia,
         1 AS nivel
    FROM tarefa t
   WHERE t.tarid = 167920
     AND t.taridinclusao IS NOT NULL
   UNION ALL
  SELECT t.taridinclusao AS tarefa_inclusao,
         h.tarefa_referencia,
         h.nivel + 1
    FROM historico h
         INNER JOIN tarefa t ON t.tarid = h.tarefa_inclusao
   WHERE h.tarefa_inclusao IS NOT NULL
     AND t.taridinclusao IS NOT NULL
)
UPDATE t
   SET t.taridinclusao = h.tarefa_inclusao
  FROM tarefa t
       INNER JOIN historico h ON h.tarefa_referencia = t.tarid
 WHERE NOT EXISTS(SELECT 1
                    FROM historico h2
                   WHERE h2.nivel > h.nivel)
OPTION(MAXRECURSION 0);

In this query am updating the table tarefa based on the history generated by taking only the highest level of recursion.

  • All right, young man. Obg

Browser other questions tagged sql query sql-update sql-server-2012

You are not signed in. Login or sign up in order to post.