Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Update Syntax

Posted on 2013-11-01
3
Medium Priority
?
317 Views
Last Modified: 2013-11-01
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
Comment
Question by:trbbhm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39616951
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
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 39616960
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39617051
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question