Update statement 1

[deucesp97]

Are you not able to use a reference to another table when executing an update procedure.

I am trying to update a column but I am getting errors in my SQL statement.

I am using a DB2 database.
My example:
UPDATE W17G.INVOICE
SET REV_ROLE = 'CS'
FROM W17G.V_SCI001 INNER JOIN W17G.INVOICE ON
W17G.V_SCI001.USER_ID = W17G.INVOICE.ROUTE_LST AND
W17G.V_SCI001.VCKVAL = W17G.INVOICE.VEND_ID AND
W17G.V_SCI001.POKVAL = W17G.INVOICE.PO_NUMBER;

[sig][/sig]

 

[karluk]

My DB2 experience is not recent, but I remember having problems with joins in an update statement. The documentation indicated that it was a real limitation of DB2, rather than something I was doing wrong. I can't remember if we were on version 4.1 or 5.1 at the time.

This sounds like something that could change with every release. Your reference manuals doubtlessly indicate what the current limitations are. [sig][/sig]

 

[listener22]

Knowing zip about db2, if you have the value, 'CS',
that you want rev_role to be, is the join supposed to
play the role of a where clause, or what? Whatever
happens in the join doesn't seem to be aimed at
changing the new value for rev_role, yet there is no
where. Is that the db2 way of where, so to speak? [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]

 

[bigscouse]

Jim,

I have been wandering about that as well.

Ged. [sig]<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>Top man[/sig]

 

[deucesp97]

I figured it out late last night after a number of hours but what you want to do is a subquery within the UPDATE statement.

UPDATE W17G.INVOICE
SET REV_ROLE = 'CS'
WHERE ROUTE_LST =
(SELECT USER_ID
FROM W17G.V_SCI001
WHERE W17G.V_SCI001.VCKVAL = W17G.INVOICE.VEND_ID AND
W17G.V_SCI001.POKVAL = W17G.INVOICE.PO_NUMBER);


[sig][/sig]