plsql update with join

Hi,

I created a update inner join as below (as per examples I've seen) and that seems to work fine

UPDATE table1 c
SET c.name =
      ( SELECT t.name
        FROM table2 t
        WHERE c.id = t.id
      )
WHERE EXISTS
        ( SELECT 1
          FROM table2 t
          WHERE c.id = t.id 
        )

Open in new window


However, that is not the whole story, since I need to be able to apply a further filter, but that filter needs to be on the table1 table. Basically that filter would look like

INNER JOIN table3 k on k.id = c.id
WHERE k.id2 = 1 and k.id3 = 2

Open in new window


so that I'm not updating the entire table1 but only a subset of table1 based on the join with table3 (if that makes sense)...

where does that third join go in this query?

thanks, Aiden
AidenAAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I had written this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

anyhow, you can add indeed more EXISTS() and conditions ...
UPDATE table1 c
SET c.name =
      ( SELECT t.name
        FROM table2 t
        WHERE c.id = t.id
      )
WHERE EXISTS
        ( SELECT 1
          FROM table2 t
          WHERE c.id = t.id 
        )
   AND EXISTS 
      ( SELECT 1
             FROM  table3 k 
               WHERE k.id = c.id
                AND k.id2 = 1 
          AND k.id3 = 2
   )

Open in new window


INNER JOIN
0
 
Devinder Singh VirdiConnect With a Mentor Lead Oracle DBA TeamCommented:
Looks like you are doing bulk update, therefore, if table is very big, you may have some kind of performance problem.
Therefore another method you can use is to create Temp table with required columns. Then use update command.
0
 
AidenAAuthor Commented:
thanks for that, that seemed to work fine, thanks for link to your article, I'll take a look. Was a bit slow though, will keep your comments in mind
0
All Courses

From novice to tech pro — start learning today.