February 11, 2008 at 12:55 pm
Hi All:
I'm trying to zero out quanties on order in Inventory for a certain vendor. My first SQL is to run a report to verify that I have the list of Inventory rows that I want to update. My SQL for the report works fine; however my SQL2 for the update does not work.
Please help - thanks
Bill
SQL1: (report)
select t1.INV_ID, t1.INV_QTYONORDER, t1.inv_partnumber, t1.INV_DESCRIPTION1,
t2.INVSUP_SUPPNUM, t2.INVSUP_INVID
from inventory t1
full outer join inventory_supplier t2
on (t1.INV_ID = t2.INVSUP_INVID)
where t1.INV_QTYONORDER <> 0
and t2.INVSUP_SUPPNUM = 11
SQL2: (update)
update inventory
set inventory.INV_QTYONORDER = 0
full outer join inventory_supplier
on (inventory.INV_ID = inventory_supplier.INVSUP_INVID)
where inventory.INV_QTYONORDER <> 0
and inventory_supplier.INVSUP_SUPPNUM = 11
February 11, 2008 at 1:15 pm
Try
update i
set i.INV_QTYONORDER = 0
from inventory i
full outer join inventory_supplier
on (i.INV_ID = inventory_supplier.INVSUP_INVID)
where i.INV_QTYONORDER <> 0
and inventory_supplier.INVSUP_SUPPNUM = 11
HTH
Dave J
February 11, 2008 at 1:17 pm
You're missing the FROM
update inventory
set inventory.INV_QTYONORDER = 0
FROM INVENTORY --<<change here
full outer join inventory_supplier
on (inventory.INV_ID = inventory_supplier.INVSUP_INVID)
where inventory.INV_QTYONORDER <> 0
and inventory_supplier.INVSUP_SUPPNUM = 11
I also wouldn't run an update on a FULL OUTER JOIN. Change that to a LEFT OUTER JOIN (there has to be a record there to update, so the "left" side can't afford to be null).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 11, 2008 at 4:02 pm
Thanks for the suggestions - it worked!!!
Bill
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply