Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

SQL Update Syntax

How can I re-write the following query so that it updates the ITEMLOC.AVERAGEUNITCOST = ITEM.COSTLASTPAID where COSTLASTPAID<>0 and AVERAGEUNITCOST=0?


select item.itemnum,costlastpaid,locationnum, itemloc.averageunitcost from "ITEM"
inner join itemloc on item.itemnum=itemloc.itemnum
where costlastpaid<>0 and itemloc.averageunitcost=0
0
trbbhm
Asked:
trbbhm
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
UPDATE ITEMLOC
SET il.AVERAGEUNITCOST = i.COSTLASTPAID
FROM ITEMLOC il
   JOIN  ITEM i on i.itemnum=il.itemnum 
WHERE costlastpaid<>0 and il.averageunitcost=0

Open in new window

0
 
sammySeltzerCommented:
Update ITEM set  ITEMLOC.AVERAGEUNITCOST = ITEM.COSTLASTPAID 
 from item inner join itemloc on item.itemnum=itemloc.itemnum 

where  costlastpaid<>0 and itemloc.averageunitcost=0

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade, which put me over the top for Genius in MS SQL Server.
Good luck with your project.

Jim
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now