Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 955
  • Last Modified:

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

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
david_m_jacobson
Asked:
david_m_jacobson
1 Solution
 
Jerry MillerCommented:
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
 
Geert GruwezOracle dbaCommented:
>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
david_m_jacobsonAuthor Commented:
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
 
Geert GruwezOracle dbaCommented:
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

Industry Leaders: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now