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
Now, here is my SQL code.
set contact_id =
order_contact, auto_eform ae
where substr(ae.subject, 10, (instr(ae.subject, ' ', 10)-10)) = order_contact.invoice_no
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!