Solved

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

Posted on 2013-01-15
5
901 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
5 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
>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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
0
 

Author Comment

by:david_m_jacobson
Comment Utility
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 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Read about achieving the basic levels of HRIS security in the workplace.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now