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 ).
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 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 JOIN
You can do this:
UPDATE AlfaGood ag
SET ag.name = 'New text goes here'
--FROM AlfaGood ag -- not an Oracle syntax
WHERE ag.agrid = 'Thats my original text'
AND ag.id = 1
AND EXISTS ( SELECT ca.id FROM SecondAlfa ca WHERE ca.id = 1 );
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 ...
Related Topics
SQL Server Trigger Insert Values from New Row into Another Table
Pivoting of Data Using Two Columns
Automate Version Number Retrieval from .Dtsx Files
Differencebetween SQL, Pl-SQL and T-Sql
Mysql: Group_Concat with Left Join
Joining Multiple Tables in SQL
Sql: How to Select Only Groups That Do Not Contain a Certain Value
Retrieve Inserted Row Id in SQL
Count Returning Blank Instead of 0
Postgres Not Using Index When Index Scan Is Much Better Option
The Used Select Statements Have a Different Number of Columns (Redux!!)
SQL Performance: Where VS Where(Row_Number)
T-SQL and the Where Like %Parameter% Clause
How to Create and Query Linked Database Servers in SQL Server