Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-01-15
5
Medium Priority
?
953 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 38

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 38

Accepted Solution

by:
Geert Gruwez earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

604 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