Update with select CTE Error

Question

Update with select CTE Error

Navigation

#1 by (1 votes)

0

I'm having a hard time performing an update with select CTE. I want to refresh the taridinclusao field. Below is the query and just below is the update that I tried to perform, however, the following error occurs: "The 'historical' derived table is not updateable 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 sql-update query sql-server-2012

asked by anonymous 27.09.2017 / 18:54

1 answer

Send data without refresh PHP [closed] Where did the console _POST sampler stop in the new firefox?

score 1 · Accepted Answer

Do not update the field in CTE but in its tarefa table 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 I am updating the tarefa table based on the generated histories taking only the highest level of recursion.