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
 updating single rows

Author  Topic 

seeker62
Starting Member

40 Posts

Posted - 2013-04-17 : 09:58:32
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

808 Posts

Posted - 2013-04-17 : 10:17:32
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.

Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 2013-04-17 : 10:30:21
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

52326 Posts

Posted - 2013-04-18 : 02:28:46
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

700 Posts

Posted - 2013-04-18 : 07:54:23
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
   

- Advertisement -