Postgres allows:
UPDATE dummy
SET customer=subquery.customer,
address=subquery.address,
partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
FROM /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;
This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
Answer from Andrew Lazarus on Stack OverflowPostgres allows:
UPDATE dummy
SET customer=subquery.customer,
address=subquery.address,
partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
FROM /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;
This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
You're after the UPDATE FROM syntax.
UPDATE
table T1
SET
column1 = T2.column1
FROM
table T2
INNER JOIN table T3 USING (column2)
WHERE
T1.column2 = T2.column2;
References
- Code sample here: GROUP BY in UPDATE FROM clause
- And here
- Formal Syntax Specification
Update fastest way
Update column in a very large table (10M records)
postgresql - Update Oracle DB table from Postgres/PostGIS DB query - Geographic Information Systems Stack Exchange
[PostgreSQL] Update query not working
Hello,
We have to update a column value(from numbers like '123' to codes like 'abc' by looking into a reference table data) in a partitioned table with billions of rows in it, with each partition having 100's millions rows. As we tested for ~30million rows it's taking ~20minutes to update. So if we go by this calculation, it's going to take days for updating all the values. So my question is
-
If there is any inbuilt way of running the update query in parallel (e.g. using parallel hints etc.) to make it run faster?
-
should we run each individual partition in a separate session (e.g. five partitions will have the updates done at same time from 5 different sessions)? And will it have any locking effect or we can just start the sessions and let them run without impacting our live transactions?
UPDATE tab_part1
SET column1 = reftab.code
FROM reference_tab reftab
WHERE tab_part1.column1 = subquery.column1;
Hello, I'm trying to set a hash value on a column in a table which contains approx. 10 millions records. I tried many queries — no success. Btw, yes I've an index on cook_id in table.
Initially I tried this request :
WITH ranked_cook AS (
SELECT
cook_id,
DENSE_RANK() OVER (ORDER BY cook_id) AS hash
FROM
table
WHERE cook_id IS NOT NULL
)
UPDATE table
SET hash = ranked_cook.hash
FROM ranked_cook
WHERE table.cook_id = ranked_cook.cook_id;Left my request all night — Woke up, it wasn't finish...so I cancelled it and told myself that it might be because of CTE which takes too long? Then I tried by creating a temp table, which took approx. 50min but finished then I tried to update from temp table but again left the query all night and wasn't finished when I woke up...
CREATE TEMP TABLE temp_ranked_cook AS
SELECT
cook_id,
DENSE_RANK() OVER (ORDER BY cook_id) AS hash
FROM
table
WHERE
cook_id IS NOT NULL;
CREATE INDEX idx_temp_cook_id ON temp_ranked_cook(cook_id);
UPDATE table
SET hash = trc.hash
FROM temp_ranked_cook trc
WHERE table.cook_id = trc.cook_id;Then I try to update by batches of 100 000 records, but still too long... Any idea?
You can use PostgreSQL Foreign Data Wrappers (FDW) to read and write to other databases. Here is and overview of FDW that I have found useful.
There seems to be a maintained and fairly feature complete implementation for Oracle.
What the FDW does is create a local "table" that is really a connection to the foreign table. You can then use it as you would a local table with SELECTS, INSERTS, etc.
I've used FDWs extensively and think they're great. They make working with multiple databases a breeze. That said, I haven't used one with Oracle before.
As commented by @Vince:
Choose one RDBMS or the other, but messing around with bridging between them is just asking for trouble. You don’t need Spatial to use the SDO_GEOMETRY datatype.
and as clarified by @AlbertGodfriend:
All 2D vector data processing comes with all Oracle database licences under the name “Oracle Locator”. Free of charge. No need for Oracle Spatial Licences.