Juan Carlos Oropeza Juan Carlos Oropeza - 1 month ago 5
SQL Question

Can I update a cte?

Using CTE I try doing

WITH cte as (
SELECT myFieldName,
row_number() over (order by gps_device_id) as rn
FROM tracker.gps_devices
)
UPDATE cte
SET cte.myFieldName = CASE WHEN cte.rn % 3 = 0 THEN '0'
WHEN cte.rn % 3 = 1 THEN '1'
WHEN cte.rn % 3 = 2 THEN '2'
END


But got the following error.


ERROR: relation "cte" does not exist


Looks like I can do
INSERT
and
DELETE
after
WITH
but
UPDATE
only inside the cte, is that correct? Im sure I did something like this, but maybe was in a different db.

https://www.postgresql.org/docs/9.6/static/queries-with.html

So I end with this, even when work is very confusing, any suggestion?.

UPDATE tracker.gps_devices g
SET g.myFieldName = CASE WHEN t.rn % 3 = 0 THEN '0'
WHEN t.rn % 3 = 1 THEN '1'
WHEN t.rn % 3 = 2 THEN '2'
END
FROM (SELECT gps_device_id,
myFieldName,
row_number() over (order by gps_device_id) as rn
FROM tracker.gps_devices) as t
WHERE g.gps_device_id = t.gps_device_id

Answer

You can use cte for update, e.g. (assuming that id is a primary key):

with cte as ( 
    select 
        id, 
        my_field_name, 
        row_number() over (order by gps_device_id) as rn
    from gps_devices
) 
update gps_devices
set my_field_name = (rn % 3)::text
from cte
where gps_devices.id = cte.id;

You can insert, update or delete rows of a table (view) rather than a resultset of a query.

Comments