Solved

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

Posted on 2013-01-15
5
937 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
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

696 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