Solved

Oracle syntax for SQL update statement with a join in the where clause

Posted on 2013-01-15
5
946 Views
Last Modified: 2013-01-16
I have a SQL statement that would work but is it possible to eliminate the subquery?

Here is the SQL statement:

UPDATE
  table_1
SET
  col_1 = 'x'
WHERE
  col_2 = 'a'
  and col_3 = 'b'
  and col_4 in (SELECT col_4 FROM table_2 WHERE table_2.col_5 = 'c')

In other words, I want to make sure that table_1.col_4 is a value that is determined from table_2.  Is this the best way to write this query or is there a way to join table_2 to eliminate the sub-query?
0
Comment
Question by:david_m_jacobson
[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
5 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 38781281
I think that this would work, but I am not at work to test it.

UPDATE   table_1
SET   col_1 = 'x'
Inner Join table_2  On .col_4 = table_1 .col_4
WHERE   table_1.col_2 = 'a'
  and table_1.col_3 = 'b'
  and table_2.col_5 = 'c')
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38781603
>jmiller1979
you've got a ) too much at the end

what is the problem with it being a subquery ?
you can alias the update table

UPDATE   table_1 A
SET   col_1 = 'x'
WHERE   A.col_2 = 'a'
  and A.col_3 = 'b'
  and exists (select null from table_2 B where B..col_5 = 'c' and A.col_4 = B.col_4)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38782164
0
 

Author Comment

by:david_m_jacobson
ID: 38783018
As far as I can tell, it's not possible in Oracle to use an inner join.  That is basically what I was trying to do.

jmiller1979, can you confirm?

Geert_Gruwez, thank you for the suggestion.  I am familiar with the "exists" option but I was trying to use a join, which does not seem possible in Oracle.

angelIII, I had already read that post which is not directly related to what I am trying to do.  The post you suggest reading is getting the value to update from another table.  In my case, I am not getting the value to update from another table.  I am just trying to use another table in the where clause. So that post does not really apply to my question.
0
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 38783253
sure an inner join is possible
but you need to do it like sql server then >> adding the update table in the select again
and using the primary key (or selection criteria)

UPDATE   table_1 A
SET   col_1 = 'x'
WHERE  A.prim_key in
(select AA.prim_key  from table_1 AA, table_2 B
  WHERE AA.col_2 = 'a'
    and AA.col_3 = 'b'
    and B.col_5 = 'c'
    and AA.col_4 = B.col_4)

or
UPDATE   table_1 A
SET   col_1 = 'x'
WHERE  A.prim_key in
(select AA.prim_key  
  from table_1 AA
    join table_2 B on AA.col_4 = B.col_4
  WHERE AA.col_2 = 'a'
    and AA.col_3 = 'b'
    and B.col_5 = 'c' )
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

717 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