I isolatet the problem from a much more complex query. Here the test scenario
DROP TABLE test;
CREATE TABLE test (
id integer,
description varchar(100)
);
INSERT INTO test(id, description) VALUES (1,'new');
INSERT INTO test(id, description) VALUES (2,'new');
If I run the query:
SELECT * FROM test WHERE id IN (UPDATE test set description='test' RETURNING id)
I'm getting the following error:
ERROR: syntax error at or near "test" LINE 1: SELECT * FROM test WHERE id (UPDATE test set description='test' RE... ^
*** Fehler ***
ERROR: syntax error at or near "test" SQL Status:42601 Zeichen:37
However if I only run the statemennt
UPDATE test set value='test' RETURNING id
I get a result with 2 rows:
1 2
If I substitude that result I would have a query like:
SELECT * FROM test WHERE id IN (1,2);
with the result:
1;"test" 2;"test"
Why do I not get the same result with my initial statement?
The SELECT FOR UPDATE statement is used to order transactions by controlling concurrent access to one or more rows of a table. It works by locking the rows returned by a selection query, such that other transactions trying to access those rows are forced to wait for the transaction that locked the rows to finish.
The UPSERT statement is a DBMS feature that allows a DML statement's author to either insert a row or if the row already exists, UPDATE that existing row instead. That is why the action is known as UPSERT (simply a mix of Update and Insert).
The PostgreSQL UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update the selected rows. Otherwise, all the rows would be updated.
Before PostgreSQL 9.1 INSERT/UPDATE/DELETE could only be used as top level statements. This is why you are getting a syntax error.
Starting from 9.1 you can use data-modifying statements with common table expressions. Your example query would look like this:
WITH updated AS (UPDATE test SET description = 'test' RETURNING id)
SELECT * FROM test WHERE id IN (SELECT id FROM updated);
Be careful with selecting from the just modified table. You can get confusing results that way. Becuse the queries are executed in the same snapshot, the SELECT will not see the effects of the UPDATE statement.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With