0

I have 3 tables, say RELATIONS,PERSON,COMPANY I need to UPDATE active_flag field of PERSON to 'I'/'A' by joining these 3 tables. My requirement is like

UPDATE PERSON p
SET p.active_flag ='I'
FROM PERSON p,RELATIONS r,COMPANY c
WHERE p.email ='email@gmail.com'
AND c.rel_id = r.id
AND r.dep_id = '1234567'
AND r.book_id = '1234567'

How can I do that??

Burhan Ali
  • 2,258
  • 1
  • 28
  • 38
Nidheesh
  • 4,390
  • 29
  • 87
  • 150
  • 2
    please mention the relation between PERSON and RElation or Persion and COMPANY table. – Maulik Vora Aug 16 '12 at 07:50
  • possible duplicate of [Oracle - Update statement with inner join](http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join) – Ben Aug 16 '12 at 10:51
  • 1
    Your query is also (probably) incorrect. It contains a Cartesian join between `person` and `relations` and `company`... you probably want to join these together somehow? – Ben Aug 16 '12 at 10:52

2 Answers2

1

--Not sure about joins

UPDATE p
SET 
    p.[active_flag] ='I'
FROM Person p
    INNER JOIN Relations r ON
        r.[vNumber] = p.[vesionNo]
    INNER JOIN Company c ON
        c.[rel_id] = r.[id]
WHERE
    p.[email] ='email@gmail.com'
AND r.[dep_id] = '1234567' 
AND r.[book_id] = '1234567'
FarligOpptreden
  • 5,013
  • 22
  • 23
Landi
  • 651
  • 1
  • 6
  • 9
  • 1
    Sorry but this is incorrect. It's SQL-Server syntax _not_ Oracle. Also you cannot do updates with joins in Oracle. – Ben Aug 16 '12 at 10:49
1
merge into person p
using (
       select id
       FROM PERSON p,RELATIONS r,COMPANY c
       WHERE p.email ='email@gmail.com'
             AND c.rel_id = r.id
             AND r.dep_id = '1234567'
             AND r.book_id = '1234567'
      ) pu
on (pu.id = p.id)
when matched then update set p.active_flag ='I'
Alexander Tokarev
  • 1,000
  • 7
  • 16