Update with Join Query in Oracle

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

Update statement with concat & inner join on Oracle

CONCAT accepts only two parameters, which means that you have to use nested CONCATs.

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 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 );

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)

Update with Join query in Oracle

Your query does not make a whole lot of sense with the generic table1, table2, and join_key references.

If this is not what you are looking for, it would be helpful to have some sample data to get a better idea of what results you are looking for.

update table1 t1
   set t1.col = (select t2.col
                 from table2 t2
                 where  t1.join_key = t2.join_key(+)  
                  and  t1.col is not null),
       t1.output = (select t2.output + t1.col
                    from  table2 t2
                   where  t1.join_key = t2.join_key(+)  
                     and  t1.col is not null);

---

Related Topics