SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 updating single rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

seeker62
Starting Member

40 Posts

Posted - 04/17/2013 :  09:58:32  Show Profile  Reply with Quote
I am using the following update to place values in a table but it places the values for each record that fits the criteria. I want it to update only one of the many:

UPDATE EP_DataWarehouse.dbo.RptPortfolioCubeData
SET contractedvolume = populate.tons
FROM EP_DataWarehouse.dbo.RptPortfolioCubeData pcd
INNER join
(select quantity/2000.0 AS tons, final.ContractNum, final.mbd, final.ybd
FROM (SELECT quantity, pcd.contractnum, MONTH(idq.begindate) AS mbd, YEAR(idq.begindate) AS ybd
FROM EP_Main.dbo.ItemDetailQty idq
INNER JOIN ep_main.dbo.ContractHeader ch ON ch.ContractID = idq.RecordID
INNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData pcd ON ch.contractnum = pcd.contractnum
WHERE EXISTS (SELECT subidq.quantity
FROM ep_main.dbo.ItemDetailQty subIdq
INNER JOIN ep_main.dbo.ContractHeader subch ON subch.ContractID = subidq.RecordID
INNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData subpcd ON subch.contractnum = subpcd.contractnum
WHERE MONTH(pcd.loaddate) != MONTH(subidq.begindate) AND pcd.contractnum = subpcd.contractnum)
AND pcd.loaddate BETWEEN idq.BeginDate AND idq.EndDate AND pcd.producttype = 'co2' AND idq.TableName = 'ContractHeader') AS Final
GROUP BY quantity, contractnum, mbd, ybd
ORDER BY contractnum, ybd, mbd ) AS populate ON pcd.ContractNum = populate.ContractNum AND MONTH(pcd.loaddate) = populate.mbd AND YEAR(pcd.loaddate) = populate.ybd
--WHERE contractnum = final.contractnum AND MONTH(loaddate) = final.mpd AND YEAR(loaddate) = final.ybd
WHERE pcd.ProductType = 'co2'

the table that is being updated has following data
quantity contractnum mbd ybd so
NULL PC01200211 11 2008 123456
NULL PC01200211 11 2008 234567
NULL PC01200211 11 2008 345678

the above update places values for quantity in all three records. I want it to only place in first one. what change to the update script above is needed to accomplish this. thanks.

Hommer
Aged Yak Warrior

766 Posts

Posted - 04/17/2013 :  10:17:32  Show Profile  Reply with Quote
could you use where clause to select the only one that has 123456?

then you should be able to update that one as you prefer.

quote:
Originally posted by seeker62

I am using the following update to place values in a table but it places the values for each record that fits the criteria. I want it to update only one of the many:

UPDATE EP_DataWarehouse.dbo.RptPortfolioCubeData
SET contractedvolume = populate.tons
FROM EP_DataWarehouse.dbo.RptPortfolioCubeData pcd
INNER join
(select quantity/2000.0 AS tons, final.ContractNum, final.mbd, final.ybd
FROM (SELECT quantity, pcd.contractnum, MONTH(idq.begindate) AS mbd, YEAR(idq.begindate) AS ybd
FROM EP_Main.dbo.ItemDetailQty idq
INNER JOIN ep_main.dbo.ContractHeader ch ON ch.ContractID = idq.RecordID
INNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData pcd ON ch.contractnum = pcd.contractnum
WHERE EXISTS (SELECT subidq.quantity
FROM ep_main.dbo.ItemDetailQty subIdq
INNER JOIN ep_main.dbo.ContractHeader subch ON subch.ContractID = subidq.RecordID
INNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData subpcd ON subch.contractnum = subpcd.contractnum
WHERE MONTH(pcd.loaddate) != MONTH(subidq.begindate) AND pcd.contractnum = subpcd.contractnum)
AND pcd.loaddate BETWEEN idq.BeginDate AND idq.EndDate AND pcd.producttype = 'co2' AND idq.TableName = 'ContractHeader') AS Final
GROUP BY quantity, contractnum, mbd, ybd
ORDER BY contractnum, ybd, mbd ) AS populate ON pcd.ContractNum = populate.ContractNum AND MONTH(pcd.loaddate) = populate.mbd AND YEAR(pcd.loaddate) = populate.ybd
--WHERE contractnum = final.contractnum AND MONTH(loaddate) = final.mpd AND YEAR(loaddate) = final.ybd
WHERE pcd.ProductType = 'co2'

the table that is being updated has following data
quantity contractnum mbd ybd so
NULL PC01200211 11 2008 123456
NULL PC01200211 11 2008 234567
NULL PC01200211 11 2008 345678

the above update places values for quantity in all three records. I want it to only place in first one. what change to the update script above is needed to accomplish this. thanks.


Edited by - Hommer on 04/17/2013 10:18:01
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 04/17/2013 :  10:30:21  Show Profile  Reply with Quote
that might work except what i included in this topic is just a sample of a table which has over 20,000 records. so the update needs to cycle through that table and determine which record to place the value and then move to the next single value which fits the criteria and populate the value for the single record of that set. I did a fetch and while fetch == 0 and it took over 2 hours to update records. I can not have that.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/18/2013 :  02:28:46  Show Profile  Reply with Quote
i think what you can do is add a logic to generate rownumber based on your group (contractnum, mbd, ybd) and then order by so field. then add a condition rn = 1 to do update for only one record among the group.

UPDATE pcd
SET contractedvolume = populate.tons	
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY pcd.ContractNum,DATEADD(mm,DATEDIFF(mm,0,pcd.loaddate),0) ORDER BY pcd.loaddate) AS Seq,* 
      FROM EP_DataWarehouse.dbo.RptPortfolioCubeData) pcd
INNER join
(select quantity/2000.0 AS tons, final.ContractNum, final.mbd, final.ybd
FROM (SELECT quantity, pcd.contractnum, MONTH(idq.begindate) AS mbd, YEAR(idq.begindate) AS ybd
FROM EP_Main.dbo.ItemDetailQty idq
INNER JOIN ep_main.dbo.ContractHeader ch ON ch.ContractID = idq.RecordID
INNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData pcd ON ch.contractnum = pcd.contractnum
WHERE EXISTS (SELECT subidq.quantity
FROM ep_main.dbo.ItemDetailQty subIdq
INNER JOIN ep_main.dbo.ContractHeader subch ON subch.ContractID = subidq.RecordID
INNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData subpcd ON subch.contractnum = subpcd.contractnum
WHERE MONTH(pcd.loaddate) != MONTH(subidq.begindate) AND pcd.contractnum = subpcd.contractnum)
AND pcd.loaddate BETWEEN idq.BeginDate AND idq.EndDate AND pcd.producttype = 'co2' AND idq.TableName = 'ContractHeader') AS Final
GROUP BY quantity, contractnum, mbd, ybd
ORDER BY contractnum, ybd, mbd	) AS populate ON pcd.ContractNum = populate.ContractNum AND MONTH(pcd.loaddate) = populate.mbd AND YEAR(pcd.loaddate) = populate.ybd
--WHERE contractnum = final.contractnum AND MONTH(loaddate) = final.mpd AND YEAR(loaddate) = final.ybd
WHERE pcd.ProductType = 'co2'
AND pcd.Seq=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ifor
Aged Yak Warrior

548 Posts

Posted - 04/18/2013 :  07:54:23  Show Profile  Reply with Quote
1. Your query will not work as you have an ORDER BY in a derived table without a TOP or FOR XML.
2. I do not see the point of grouping by quantiy - presumably you want to SUM by quanity.
3. Your query is poorly formated and so difficult to maintain.

I would approach the problem as below although, as you have not provided test data in a consumable format, it is difficult to tell.


WITH Final
AS
(
	SELECT idq.quantity
		,pcd.contractnum
		,DATEADD(m, DATEDIFF(m, 0, idq.begindate), 0) AS bd
	FROM ep_main.dbo.itemdetailqty idq 
		JOIN ep_main.dbo.contractheader ch 
			ON ch.contractid = idq.recordid 
		JOIN ep_datawarehouse.dbo.rptportfoliocubedata pcd 
			ON ch.contractnum = pcd.contractnum
	WHERE pcd.loaddate BETWEEN idq.begindate AND idq.enddate 
		AND pcd.producttype = 'co2'
		AND idq.tablename = 'ContractHeader'
		AND EXISTS
		(
			SELECT 1
			FROM ep_main.dbo.itemdetailqty subIdq
			WHERE DATEADD(m, DATEDIFF(m, 0, pcd.loaddate), 0) <> DATEADD(m, DATEDIFF(m, 0,subidq.begindate), 0)
				AND EXISTS
				(
					SELECT 1
					FROM ep_main.dbo.contractheader subch
					WHERE subch.contractid = subidq.recordid
						AND subch.contractnum = pcd.contractnum
				)
		)
)
,Populate
AS
(
	SELECT SUM(quantity / 2000.0) AS tons
		,contractnum, bd
	FROM Final
	GROUP BY contractnum, bd
)
,UpdOrder
AS
(
	SELECT *
		,ROW_NUMBER() OVER (PARTITION BY contractnum, mbd, ybd ORDER BY so) AS rn
	FROM ep_datawarehouse.dbo.rptportfoliocubedata
)
UPDATE U
SET ontractedvolume = P.tons 
FROM UpdOrder U
	JOIN Populate P
		ON U.contractnum = P.contractnum
			AND U.ybd = YEAR(P.bd)
			AND U.mbd = MONTH(P.bd)
WHERE U.rn = 1;

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000