• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Conditional Oracle SQL Update statement

Hi, I'm back once again to display my cluelessness to the world :)

I was wondering if anyone could tell me the best way to compose an SQL UPDATE statement.
I'll describe the situation I've got.

We have two tables relevant to the problem:

Table name ORDER_CONTACT
Fields: INVOICE_NO (text), ORDER_CONTACT_ID (number)

Table name AUTO_EFORM
Fields: CONTACT_ID (number), SUBJECT (text)

the text in the SUBJECT field of each record in AUTO_EFORM contains an invoice number. We will be isolating that invoice number using code as you'll see below.


What I'm trying to achieve is this (forgive the pseudocode)

If <invoice number within AUTO_EFORM.SUBJECT> = ORDER_CONTACT.INVOICE_NO
and ORDER_CONTACT.SO_CONTACT_ID is not blank
then replace AUTO_EFORM.CONTACT_ID 
with ORDER_CONTACT.SO_CONTACT_ID

Open in new window


Now, here is my SQL code.

update auto_eform
set contact_id = 
select 
order_contact_id 
from 
order_contact, auto_eform ae
where substr(ae.subject, 10, (instr(ae.subject, ' ', 10)-10)) = order_contact.invoice_no

Open in new window


The code almost works properly, but it does not cater for cases where order_contact.contact_id is null.
Can anyone tell me how to adapt it so that the UPDATE only takes place if order_contact.contact_id has a valid value please?

Thanks for looking!
0
vistamed
Asked:
vistamed
2 Solutions
 
slightwv (䄆 Netminder) Commented:
When dealing with two tables and an update statement, I immediately think of MERGE:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

You can add the logic you want to the ON clause.
0
 
vistamedAuthor Commented:
Hi Slightvw,
Oooooo I am liking the look of that! I'll know better on Monday cos it's HOMETIME YAAAY and I'm running out the door, but I'd say you've got a winner there :) Thank you for your help!
0
 
Daniel WilsonCommented:
www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

Looks like you're really close.  What if you add another condition to the WHERE clause?

update auto_eform
set contact_id = 
select 
order_contact_id 
from 
order_contact, auto_eform ae
where substr(ae.subject, 10, (instr(ae.subject, ' ', 10)-10)) = order_contact.invoice_no
and  order_contact_id is not null

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
awking00Commented:
I also like the merge concept. You might try like the following:
merge into auto_eform ae
using
(select contact_id, invoice_no
 from order_contact
 where contact_id is not null) oc
on oc.invoice_no = substr(ae.subject,10, instr(ae.subject,' ',10) -10))
when matched then
update set ae.contact_id = oc.contact_id;

If this is satisfactory to you, please give slightwv credit and not me.
0
 
slightwv (䄆 Netminder) Commented:
>>please give slightwv credit and not me

I'm fine with a split since you did the actual legwork!!!  ;)
0
 
vistamedAuthor Commented:
Hi, awking00's MERGE statement was pretty much perfect - the only tweak it needed to run was to add parentheses around the conditions after the ON keyword. Once they were added, I was on the pig's back. Thanks to you both, slightwv and awking00!
Daniel, I had been struggling with the same approach you were suggesting for quite a while but I just couldn't make it work I'm afraid. The SQL grammar fairy just didn't seem to like the fact that I was referring to a table (order_contact) in the UPDATE statement when that table had originated in the SELECT statement, if you know what I mean. Thank you for your help though!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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