Update with Result from Cte (Postgresql)

Update with result from cte (postgresql)

Try your UPDATE with the following syntax;

UPDATE job
SET jobdate = cte.date
FROM cte
WHERE job.jobid = cte.jobid

Update using Common Table Expression

As documented, the UPDATE cannot yet see the rows inserted in the same statement.

The trick is to calculate the sum first, right when you insert the row into testsales.

PostgreSQL:how to update rows in CTE

with cte as(
select ....... from aTable
),update_cte as(
update cte set aField=(select somthing from cte1)
)

You can't do that.

An UPDATE may not reference a CTE term in PostgreSQL, as CTEs are materialized. They aren't just views over the underlying data. (That's sometimes really annoying, but that's how it is).

You can:

CREATE TEMPORARY VIEW someview AS SELECT ... FROM atable;

UPDATE someview SET afield = ...

if you want; that'll work on newer PostgreSQL versions that support automatically updatable views. I think 9.2 does.

Otherwise, I think you want something like:

WITH cte1 as (
select ..... from bTable inner join cte using(anID)
)
update aTable
set aField=(select somthing from cte1)
WHERE ... where clause from cte ...
RETURNING *;

but really, please don't call your CTE terms cte, cte1, etc. Give them useful, descriptive names that tell you what they are. It's like programs full of variables named a through x ... the next person who has to maintain your code, or anyone you ask for help, will not like it.

Update columns' values in CTE

As @smvenk mentioned, cte and mat views aren't updatable. You could move the case when to the select statement in the locations_events cte :)
For example:

CREATE MATERIALIZED VIEW collection.issue1 AS (
WITH Buffer_table AS
(
SELECT id, geom, ST_buffer(ST_transform(geom,2952),20) as buffer_geom
From locations
)

SELECT A.id,
event_id,
date,
field3,
field4,
field5,
field6,
field7,
CASE WHEN field8 IS NULL THEN 'No' ELSE 'Yes' END as field8,
CASE WHEN field9 in ('3','4') THEN 'Yes' ELSE 'No' END as field9

From events.safe_copy

CROSS JOIN LATERAL
(
Select id
FROM Buffer_table
WHERE ST_Within(ST_Transform(ST_SetSRID(ST_MakePoint("LONGITUDE", "LATITUDE"), 4326), 2952), buffer_geom)
AND date >= '2015-01-01' AND date <='2020-12-31'
AND field6 in ('2')
AND field5 in ('2')
AND field7 in ('3', '5')
AND field3 in ('1','2')
) a

Trying to use a CTE calculation to update a column

In an aggregating query all columns after SELECT must either be in the GROUP BY clause or a parameter to an aggregation function. Move the multiplication out of the CTE.

WITH cte_avg1
AS
(
SELECT avg(column2) avg
FROM table1
)
UPDATE table1
SET column3 = column1 * cte_avg1.avg
FROM cte_avg1
WHERE column1 IS NOT NULL;

Multiple update statements in postgres CTE

Yes, this is documented:

All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables.

You'll either have to rewrite the query so that each table is modified only once in the statement, or you have to run two separate statements.

Can I update a cte?

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.



Related Topics



Leave a reply



Submit