Update using Subquery, best way? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update using Subquery, best way?

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-09-08 : 11:27:12

I have to go through our master list of parts, and for everything purchased in the last 4 months that doesn't have a 'standard' unit cost, update the that field with the most recent purchase price as detailed by the PO.

I can query to identify the records that need to be updated well enough, but how would I use that query to drive an update query?

Say the tables look something like this (simplified, of course):

ItemMaster.PartNo, ItemMaster.PartRev, ItemMaster.StandardCost
POITEM.Partno, POITEM.PartRev, POITEM.UNITCOST, POITEM.PONUMBER
POMAST.PONUMBER, POMAST.ORDERDATE

The identifying query would be


SELECT 
	POITEM.PARTNO, 
	POITEM.PARTREV, 
	POITEM.UNITCOST
FROM 
	POITEM LEFT JOIN POMAST ON POITEM.FPONO = POMAST.FPONO
	LEFT JOIN ItemMaster on POITEM.PARTNO+POITEM.FPARTREV = ItemMaster.PartNo+ItemMaster.PartRev
WHERE
	POMAST.FORDDATE BETWEEN ('2010-05-01') and ('2010-09-09')
	AND ItemMaster.StandardCost in ('0.0000')

How would I go about using the results of that query to drive an update subquery?

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-09-08 : 13:03:52

Depending on the version of SQL Server you could use a CTE. Otherwise you would just need to use sub queries.

I did not QA this, but you could try something like:


WITH IdentifiedRecords (PartNo, PartRev, UnitCost)
AS
(
   SELECT 
	POITEM.partno, 
	POITEM.PARTREV, 
	POITEM.UNITCOST
   FROM 
	POITEM LEFT JOIN POMAST ON POITEM.FPONO = POMAST.fpono
	LEFT JOIN ItemMaster AS ItemMaster on   POITEM.PARTNO+POITEM.PARTREV = ItemMaster.PartNo+ItemMaster.PartRev
WHERE
	POMAST.FORDDATE BETWEEN '2010-05-01' and '2010-09-09'
	AND ItemMaster.StandardCost = '0.0000'
)

UPDATE ItemMaster
SET ItemMaster.StandardCost = (
   SELECT IdentifiedRecords.unitcost
   FROM IdentifiedRecords 
   WHERE ItemMaster.partno+ItemMaster.PARTREV = IdentifiedRecords.PartNo+IdentifiedRecords.PARTREV
                                )

BTW, this isn't Made2Manage, is it?

===
http://www.ElementalSQL.com/

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-09-08 : 18:11:01

quote:

Originally posted by RobertKaucher

Depending on the version of SQL Server you could use a CTE. Otherwise you would just need to use sub queries.

I did not QA this, but you could try something like:


WITH IdentifiedRecords (PartNo, PartRev, UnitCost)
AS
(
   SELECT 
	POITEM.partno, 
	POITEM.PARTREV, 
	POITEM.UNITCOST
   FROM 
	POITEM LEFT JOIN POMAST ON POITEM.FPONO = POMAST.fpono
	LEFT JOIN ItemMaster AS ItemMaster on   POITEM.PARTNO+POITEM.PARTREV = ItemMaster.PartNo+ItemMaster.PartRev
WHERE
	POMAST.FORDDATE BETWEEN '2010-05-01' and '2010-09-09'
	AND ItemMaster.StandardCost = '0.0000'
)

UPDATE ItemMaster
SET ItemMaster.StandardCost = (
   SELECT IdentifiedRecords.unitcost
   FROM IdentifiedRecords 
   WHERE ItemMaster.partno+ItemMaster.PARTREV = IdentifiedRecords.PartNo+IdentifiedRecords.PARTREV
                                )

BTW, this isn't Made2Manage, is it?

===
http://www.ElementalSQL.com/

As a matter of fact, it is M2M. Things are somewhat weirder in 6.0 because they 'replaced' INMAST with INMASTX, which is essentially INMAST minus the calculated fields, but then they kept INMAST around as a view with a trigger that prevents direct updates to INMAST.

So, I end up having to go to INMAST for the fonhand (because I don't want to include anything in the update that has an onhand quantity, otherwise it bypasses the inventory revaluation logic) but still hit INMASTX for the actual update.

I'm trying to work that into your example, but am not having much success due to time constraints; I accidentally discovered a complicated way to update the standard cost for every item in INMASTX with the same value, so spent a few minutes restoring the test DB (hooray for test servers).

I appreciate your help, though, hopefully I can expand on that without causing more havoc :)

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-09-09 : 13:08:24

I'm no longer a user, I changed jobs, but we were still on 5.6. Which version of SQL Server are you using?

You can feel free to contact me. My email is my SQL Team username at gmail.com. I got pretty far into the 5.6 database. Even had a huge DB diagram mapped out on my office wall.

Let me know if it works for you!

===
http://www.ElementalSQL.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources