Update Statement With Inner Join on 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 ).

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 ...

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

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

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 statement with inner join with set value

This is not correct syntax in Oracle. You can use exists as follows:

UPDATE GTP_CUSTOMER_REFERENCE GCR
SET GCR.REFERENCE='0000001'
WHERE EXISTS (SELECT 1 FROM gtp_company gc WHERE gc.abbv_name = gcr.customer_abbv_name
AND gc.name in ('AAA', 'BBB'))

Or you can use merge statement as follows:

MERGE INTO GTP_CUSTOMER_REFERENCE GCR
USING (SELECT DISTINCT gc.abbv_name AS abbv_name
         FROM gtp_company gc 
        WHERE gc.name in ('AAA', 'BBB')) GC
ON (gc.abbv_name = gcr.customer_abbv_name)
    WHEN MATCHED THEN UPDATE SET GCR.REFERENCE='0000001'

---

Related Topics