At 10:04 AM 13/02/2008, you wrote:

>Hi All:
>
>I have this SQL that I'm trying to update Qty on Order in the
>Inventory table. I only want to update for certain vendor and that's
>why I need the join.
>
>Anyway, I get an error when I try to run this.
>
>Thanks for any help
>Bill
>
>update INVENTORY
>set INVENTORY.INV_QTYONORDER = 0
>from INVENTORY
>left outer join INVENTORY_SUPPLIER
>on (INVENTORY.INV_ID = INVENTORY_SUPPLIER.INVSUP_INVID)
>where INVENTORY.INV_QTYONORDER <> 0
>and INVENTORY_SUPPLIER.INVSUP_SUPPNUM = 10

update INVENTORY i
set i.INV_QTYONORDER = 0
where exists
(select 1 from INVENTORY_SUPPLIER is
where is.INVSUP_INVID = i.INV_ID
and is.INVSUP_SUPPNUM = :suppnum
and i.INV_QTYONORDER <> 0)

Watch it when testing i.INV_QTYONORDER, if it is nullable. The last test returns false on a null, so the update wouldn't happen. But it looks like a superfluous test. You're going to set it to zero anyway. Does it make any difference if it's already zero? (e.g., yes, it would make a difference if you have a trigger that timestamps the change and you want to be able to trace when the update was first done...)

./heLen