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.
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 pcdINNER join(select quantity/2000.0 AS tons, final.ContractNum, final.mbd, final.ybdFROM (SELECT quantity, pcd.contractnum, MONTH(idq.begindate) AS mbd, YEAR(idq.begindate) AS ybdFROM EP_Main.dbo.ItemDetailQty idqINNER JOIN ep_main.dbo.ContractHeader ch ON ch.ContractID = idq.RecordIDINNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData pcd ON ch.contractnum = pcd.contractnumWHERE 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 dataquantity contractnum mbd ybd soNULL PC01200211 11 2008 123456NULL PC01200211 11 2008 234567NULL PC01200211 11 2008 345678the 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 pcdINNER join(select quantity/2000.0 AS tons, final.ContractNum, final.mbd, final.ybdFROM (SELECT quantity, pcd.contractnum, MONTH(idq.begindate) AS mbd, YEAR(idq.begindate) AS ybdFROM EP_Main.dbo.ItemDetailQty idqINNER JOIN ep_main.dbo.ContractHeader ch ON ch.ContractID = idq.RecordIDINNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData pcd ON ch.contractnum = pcd.contractnumWHERE 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 dataquantity contractnum mbd ybd soNULL PC01200211 11 2008 123456NULL PC01200211 11 2008 234567NULL PC01200211 11 2008 345678the 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.
|
|
|
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. |
|
|
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 pcdSET 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) pcdINNER join(select quantity/2000.0 AS tons, final.ContractNum, final.mbd, final.ybdFROM (SELECT quantity, pcd.contractnum, MONTH(idq.begindate) AS mbd, YEAR(idq.begindate) AS ybdFROM EP_Main.dbo.ItemDetailQty idqINNER JOIN ep_main.dbo.ContractHeader ch ON ch.ContractID = idq.RecordIDINNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData pcd ON ch.contractnum = pcd.contractnumWHERE EXISTS (SELECT subidq.quantityFROM ep_main.dbo.ItemDetailQty subIdqINNER JOIN ep_main.dbo.ContractHeader subch ON subch.ContractID = subidq.RecordIDINNER JOIN EP_DataWarehouse.dbo.RptPortfolioCubeData subpcd ON subch.contractnum = subpcd.contractnumWHERE 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 FinalGROUP BY quantity, contractnum, mbd, ybdORDER 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.ybdWHERE pcd.ProductType = 'co2'AND pcd.Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 FinalAS( 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 ) )),PopulateAS( SELECT SUM(quantity / 2000.0) AS tons ,contractnum, bd FROM Final GROUP BY contractnum, bd),UpdOrderAS( SELECT * ,ROW_NUMBER() OVER (PARTITION BY contractnum, mbd, ybd ORDER BY so) AS rn FROM ep_datawarehouse.dbo.rptportfoliocubedata)UPDATE USET 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; |
|
|
|
|
|
|
|