SQL question Invalid Identifier

[SJohnE]

Trying to write an update query that will add some text to beginning of an existing reocord.

Brief table description:
* Tags table holds material identifications that are unique, they are to be placed in a particular location inside the plant.

* PO_Lines table holds line item details for purchase orders.

* PO_Notes is a table that hold different types of notes, there are PO Header notes, PO Line footers, PO Specifications etc. Each of these are identified by a code.

The type of note that I want to add data to is the PO Line Footer which is identified by POLNFTR in the PONO_VTYP_TYPE column of the PO_Notes table.

The Error is:
ORA-00904 "PO_Lines"."POLN_SEQNO": invalid identifier.

I know for a fact that the table and colomn names are valid.

Here is the code...

Update PO_Notes

Set PO_Notes.PONO_Note = 'This Item is Export Controlled. ' || PO_Notes.PONO_Note

WHERE
TAGS.TAGS_EXPORT_CONTROLLED = 1
And PO_lines.POLN_SEQNO = 78121
And Tags.Tags_POLN_SEQNO = PO_LINES.POLN_SEQNO
And PO_LINES.POLN_SEQNO = PO_NOTES.PONO_DATASEQNO
And PO_NOTES.PONO_VTYP_TYPE = 'POLNFTR';

Can anyone explain whats wrong with my SQL statement?

 

[SantaMufasa]

SJohnE,

Your logic is solid, but the syntax that Oracle requires to do what you want is, admittedly, rather convoluted.

You and I both know that you want to read data from the TAGS and the PO_LINES tables to be able to properly UPDATE the PO_NOTES table. But Oracle only knows how to read table data via a SELECT statement.

Therefore, we must devise a SELECT statement as a sub-query that will correlate its SELECTed rows with the proper rows of your UPDATE statement. This technique is called a correlated subquery.

In the re-write of your code, I correlate the subquery in the WHERE clause through the use of a strange little Oracle relational test named "EXISTS". What this means is "For each row of your UPDATE statement, test to see if 1 or more rows EXIST in the result set of the subquery." (What gets returned from the subquery is irrelevant...that's why I use the string 'anything' as a literal return expression, but the return expression could just as easily have been any legal Oracle expression, including the Oracle function NULL.)

Here is my code re-write for your original code:

Update PO_Notes
   Set PO_Notes.PONO_Note =
          'This Item is Export Controlled. ' ||
           PO_Notes.PONO_Note
 WHERE exists (select 'anything'
                 from tags
                     ,po_lines
                where TAGS.TAGS_EXPORT_CONTROLLED = 1
                  And PO_lines.POLN_SEQNO = 78121
                  And Tags.Tags_POLN_SEQNO = PO_LINES.POLN_SEQNO
                  And PO_LINES.POLN_SEQNO = PO_NOTES.PONO_DATASEQNO
                  And PO_NOTES.PONO_VTYP_TYPE = 'POLNFTR'
               );

Let me know if it works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SJohnE]

Mufasa,
Thank you for your response. I have not yet had a chance to do this but the information you provided in prelude to the code you given is extemely helpful. I will let you know how it turns out.

Thanks Again!!!