Solved

plsql update with join

Posted on 2014-04-08
3
650 Views
Last Modified: 2014-04-08
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
0
Comment
Question by:AidenA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 39985916
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
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 100 total points
ID: 39986369
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
 

Author Closing Comment

by:AidenA
ID: 39986693
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

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need multiple Group By's 8 55
Performance Issue in Oracle 3 48
Oracle Join issue. 3 48
SQL join ...want to return one row 4 15
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question