Update statement with inner join on Oracle
That syntax isn't valid in Oracle. You can do this:
UPDATE table1 SET table1.value = (SELECT table2.CODE
FROM table2
WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
FROM table2
WHERE table1.value = table2.DESC);
Or you might be able to do this:
UPDATE
(SELECT table1.value as OLD, table2.CODE as NEW
FROM table1
INNER JOIN table2
ON table1.value = table2.DESC
WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW
It depends if the inline view is considered updateable by Oracle
( To be updatable for the second statement depends on some rules listed
here ).
How to update with inner join in Oracle
This synthax won't work in Oracle SQL.
In Oracle you can update a join if the tables are "key-preserved", ie:
UPDATE (SELECT a.val_a, b.val_b
FROM table a
JOIN table b ON a.b_pk = b.b_pk)
SET val_a = val_b
Assuming that b_pk
is the primary key of b
, here the join is updateable because for each row of A there is at most one row from B, therefore the update is deterministic.
In your case since the updated value doesn't depend upon another table you could use a simple update with an EXIST condition, something like this:
UPDATE mytable t
SET t.VALUE = 'value'
WHERE EXISTS
(SELECT NULL
FROM tableb b
INNER JOIN tablec c ON c.id = b.id
INNER JOIN tabled d ON d.id = c.id
WHERE t.id = b.id
AND d.key = 1)
SQL JOIN with UPDATE
Your join is wrong (You are using FROM in UPDATE)
This syntax for JOIN is not for ORACLE
UPDATE TBL_TEST2
SET TBL_TEST2.NAME = 'DONE'
FROM TBL_TEST2
INNER JOIN TBL_TEST1 ON TBL_TEST2.DISTRICT = TBL_TEST1.DISTRICT
WHERE TBL_TEST2.DISTRICT = '1';
In ORACLE A simple way for update joined table is based on the use the the joined select as a table
UPDATE (
SELECT TBL_TEST2.NAME AS OLD_VALUE
FROM TBL_TEST2
INNER JOIN TBL_TEST1 ON TBL_TEST2.DISTRICT = TBL_TEST1.DISTRICT
WHERE TBL_TEST2.DISTRICT = '1' ) T
SET T.OLD_VALUE = 'DONE' ;
Update statement with concat & inner join on Oracle
CONCAT
accepts only two parameters, which means that you have to use nested CONCAT
s.
Though, you'd rather use the double pipe ||
operator which doesn't have such a restriction. So:
update table1 A SET A.DESC = (SELECT B.fld1 ||':'|| B.fld2 ||':'|| B.fld3 --> this
from table2 B
where A.ID = B.ID)
Where A.id = 12;
To update all matching rows, you could
update table1 A SET A.DESC = (SELECT B.fld1 ||':'|| B.fld2 ||':'|| B.fld3 --> this
from table2 B
where A.ID = B.ID)
Where exists (select null
from table2 b
where a.id = b.id);
or MERGE
:
merge into table1 a
using table2 b
on (b.id = a.id)
when matched then update set a.desc = b.fld1 ||':'|| b.fld2 ||':'|| b.fld3;
As you got duplicates, DISTINCT
might help, e.g.
update table1 a set
a.desc = (select distinct b.fld1 ||':'|| b.fld2 ||':'|| b.fld3
from table2 b
where a.id = b.id
)
where exists ...
If not, then you'll have to see what to do with these duplicates. If possible, use yet another column(s) in WHERE
clause. Or, if you don't really care which concatenated combination fits, use aggregate function(s) such as MIN
or MAX
, e.g.
update table1 a set
a.desc = (select max(b.fld1 ||':'|| b.fld2 ||':'|| b.fld3)
from table2 b
where a.id = b.id
)
where exists ...
Oracle Update statement with an Inner Join
In Oracle, you can't use a from
clause in an update
statement that way. Any of the following should work.
UPDATE d
SET d.user_id =
(SELECT c.user_id
FROM c
WHERE d.mgr_cd = c.mgr_cd)
WHERE d.user_id IS NULL;
UPDATE (SELECT d.user_id AS d_user_id, c.user_id AS c_user_id
FROM d INNER JOIN c ON d.mgr_cd = c.mgr_cd
WHERE d.user_id IS NULL)
SET d_user_id = c_user_id;
UPDATE (SELECT d.user_id AS d_user_id, c.user_id AS c_user_id
FROM d INNER JOIN c ON d.mgr_cd = c.mgr_cd)
SET d_user_id = c_user_id
WHERE d_user_id IS NULL;
However, my preference is to use MERGE
in this scenario:
MERGE INTO d
USING c
ON (d.mgr_cd = c.mgr_cd)
WHEN MATCHED THEN
UPDATE SET d.user_id = c.user_id
WHERE d.user_id IS NULL;
Oracle update value of a table using inner join with the same table
You may combine update
and select
by the help of merge
like the following statement :
MERGE INTO bplustree t1
USING
(
SELECT P.depth + 1 depth, p.node_id
FROM bplustree T INNER JOIN bplustree P
ON (T.parent_node_id = P.node_id)
WHERE P.depth >= 0 AND T.depth IS NULL
) t2
ON ( t1.node_id = t2.node_id )
WHEN MATCHED THEN UPDATE SET
t1.depth = t2.depth;
D e m o
Update column by join two tables using Oracle
In oracle, Update using JOIN
is not allowed.
You can use MERGE
. It can update one table using another table (s).
MERGE INTO T1
USING (SELECT T2.PRODUCT,
T2.ORDER
FROM T2 WHERE T2.PRODUCT LIKE '%P12%') T2
ON ( T1.ORDER = T2.ORDER )
WHEN MATCHED THEN UPDATE
SET T1.USER = T2.PRODUCT
ORACLE SQL UPDATE with INNER JOIN isn't working
Oracle does not support this MySQL-style update join syntax. But, you may use a correlated subquery instead:
UPDATE orders AS o
SET total_price_cache = (SELECT SUM(total_price) FROM order_lines ol
WHERE ol.order_id = o.order_id);
Related Topics
Combine Output Inserted.Id with Value from Selected Row
Whats Wrong with This SQL Statement for Table Variable Bulk Insert
Why Is My Case Expression Non-Deterministic
Exists/Not Exists: 'Select 1' VS 'Select Field'
Slick 3.0 - Update Columns in a Table and Return Whole Table Object
How to Turn Off Implicit Type Conversion in SQL Server
How to Use a Reserved Word in SQL as a Table Name
Hamming Weight/Population Count in T-Sql
Running Sum in Access Query with Group By
Time Attendances Query in Microsoft Access
How to "Reset" Running Sum After It Reaches a Threshold
How Use Inserted\Deleted Table in Stored Procedure
SQL Query to Select the 'Next' Record (Similar to First or Top N)
Login Failed. the Login Is from an Untrusted Domain and Cannot Be Used with Windows Authentication
Put Pg_Try_Advisory_Xact_Lock() in a Nested Subquery
Self-Referencing Constraint in Ms SQL