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
How to Import a SQL File into a Rails Database
SQL Query in Spark/Scala Size Exceeds Integer.Max_Value
Getting the Sum of a Datediff Result
How to Execute Table Valued Function
How to Select Using with Recursive Clause
Sql: When It Comes to Not in and Not Equal To, Which Is More Efficient and Why
SQL Query to Add a New Column After an Existing Column in SQL Server 2005
Access 2007: "Select Count(Distinct ..."
Finding the Data Types of a SQL Temporary Table
Most Recent Record in a Left Join
Difference Between Stored Procedures and User Defined Functions
How to Use Merge on Linked Servers
Randomly Select a Row with SQL in Access
Mysql: Select N Rows, But with Only Unique Values in One Column