Updating Multiple Rows with Varying Data

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 Multiple Rows with Varying Data

Author

Topic

kkuhns
Starting Member

7 Posts

Posted - 2008-07-07 : 17:25:25

1. Question
Is it possible to do multiple updates to a table based on results from another query? What I would like to be able to do is take the results from one query and use that as basis for my update.

2. Sample Table:
CREATE TABLE [LB] (
[WBS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WBS2] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WBS3] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LaborCode] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[HrsBud] [decimal](19, 4) NOT NULL CONSTRAINT [DF__LB__HrsBud__75634D2A] DEFAULT (0),
[BillBud] [decimal](19, 4) NOT NULL CONSTRAINT [DF__LB__BillBud__774B959C] DEFAULT (0),
[EacHrs] [decimal](19, 4) NOT NULL CONSTRAINT [DF__LB__EacHrs__7A280247] DEFAULT (0),
[BillEacAmt] [decimal](19, 4) NOT NULL CONSTRAINT [DF__LB__BillEacAmt__5B451F22] DEFAULT (0),
CONSTRAINT [LBPK] PRIMARY KEY NONCLUSTERED
(
[WBS1],
[WBS2],
[WBS3],
[LaborCode]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

3. Sample Data for LB Table:
insert into lb (WBS1,WBS2,WBS3,LaborCode,HrsBud,BillBud,EacHrs,BillEacAmt)
select '31070003.000','000','','0001',0.0000,0.0000,50.0000,5000.0000
UNION all
select '31070003.000','002','','0001',0.0000,0.0000,70.0000,70000.0000
UNION all
select '31070003.000','003','','A101',0.0000,0.0000,97.0000,97000.0000
UNION all
select '31070003.000','004','','0001',0.0000,0.0000,248.0000,2480000.0000
UNION all
select '31070003.000','005','','0001',0.0000,0.0000,47.0000,47000.0000

4. Feeble Attempts
I have tried UPDATE, but to no avail. In all online searches the only help that I can find is on basic updates ie.: UPDATE MYTable SET myname = ‘kkuhns’.

5.Expected Results:
Here is the data that I get from the other query:
WBS1,WBS2,WBS3,LaborCode,EacHrs
‘31070003.000','000','','0001',524.0000
‘31070003.000','000','','0004',30.0000
‘31070003.000','000','','0008',3.0000
‘31070003.000','000','','0111',12.0000
‘31070003.000','000','','S000',14.5000
‘31070003.000','000','','S001',68.7500
‘31070003.000','000','','S014',49.7500
‘31070003.000','000','','S200',18.5000
‘31070003.000','000','','S204',3.0000
‘31070003.000','000','','S300',35.0000
‘31070003.000','000','','S301',128.7500
‘31070003.000','000','','S303',3.0000
‘31070003.000','000','','S304',3.0000
‘31070003.000','000','','S306',11.0000
‘31070003.000','000','','S307',9.0000
‘31070003.000','000','','S308',267.0000
‘31070003.000','000','','S309',4.0000
‘31070003.000','000','','S310',2.0000
‘31070003.000','000','','S314',5.5000
‘31070003.000','000','','S340',180.2500
‘31070003.000','000','','S514',187.0000
‘31070003.000','002','','0001',18.0000
‘31070003.000','002','','S001',21.0000
‘31070003.000','002','','S300',10.0000
‘31070003.000','002','','S308',17.0000
‘31070003.000','002','','S314',4.0000
‘31070003.000','003','','0001',3.0000
‘31070003.000','003','','A101',12.0000
‘31070003.000','003','','S300',2.0000
‘31070003.000','003','','S308',1.5000
‘31070003.000','003','','S340',2.7500
‘31070003.000','003','','S500',10.0000
‘31070003.000','003','','S514',65.5000
‘31070003.000','004','','0001',68.0000
‘31070003.000','004','','A301',69.5000
‘31070003.000','004','','A330',2.0000
‘31070003.000','004','','S000',20.0000
‘31070003.000','004','','S014',10.0000
‘31070003.000','004','','S300',1.0000
‘31070003.000','004','','S301',36.5000
‘31070003.000','004','','S500',4.5000
‘31070003.000','004','','S514',36.5000
‘31070003.000','005','','0001',46.5000

What I would like to do is update the LB table with this data. As you can see table LB is actually quite small but the information that needs to be updated is about 8x larger. Where the LaborCode doesn’t exist in the LB table, but it is found in the source data for the update, I would have to insert that into the LB Table, so this might actually turn out to be a combination of an UPDATE and an INSERT. I am stuck at the crossroads of how to go from a single row update to multiple row update with varying data. A nudge in the right direction would be much appreciated.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-07-07 : 17:57:16

e.g.:


update lb
set BillEacAmt= 4500.000

where
	WBS1 ='31070003.000'
and WBS2 = '000'
and WBS3 = ''
and LaborCode = '0001'

this is just a chunk of code...you can insert all data into TB2
and perform upsert anytime you want as much as you want.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 04:41:25

If i understand you correctly you need to do it in two steps. first insert & then update. for ex, if your source table is src and destination is lb, the insert will be

insert into LB (fields)
SELECT fields
FROM src s
LEFT JOIN LB l
ON  l.LaborCode=s.LaborCode
WHERE l.LaborCode IS NULL

this will insert only those values from src whose labor codes dont exist in LB. then update can be done as follows

UPDATE l
SET l.Fields=s.Fields...
FROM LB l
INNER JOIN SRC s
ON s.LaborCode=l.LaborCode

kkuhns
Starting Member

7 Posts

Posted - 2008-07-08 : 10:21:33

Thank you for your responses. I will try this today and let you know how things are working.

kkuhns
Starting Member

7 Posts

Posted - 2008-07-10 : 14:14:10

Well it has now been 2 days, and I believe that I have partially worked through the first half.

Here is what I ended up using:

INSERT INTO LB (wbs1, wbs2, wbs3, laborcode, etchrs)
SELECT LD.WBS1 as wbs1, LD.WBS2 as wbs2, LD.WBS3 as wbs3, LD.LaborCode as laborcode, SUM(LD.RegHrs + LD.OvtHrs) AS etchrs
FROM PR INNER JOIN
PR AS PR_2 ON PR.WBS1 = PR_2.WBS1 INNER JOIN
PR AS PR_3 ON PR_2.WBS1 = PR_3.WBS1 AND PR_2.WBS2 = PR_3.WBS2 INNER JOIN
LD ON PR_3.WBS1 = LD.WBS1 AND PR_3.WBS2 = LD.WBS2 AND PR_3.WBS3 = LD.WBS3 INNER JOIN
LB ON PR_3.WBS1 = LB.WBS1 AND PR_3.WBS2 = LB.WBS2 AND PR_3.WBS3 = LB.WBS3
WHERE (PR.WBS2 = '') AND (PR.WBS3 = '') AND (PR.WBS1 = '31070003.000') AND (PR_2.WBS3 = '') and LD.laborcode not in (Select lb.laborcode from LB where lb.wbs1='31070003.000')
GROUP BY LD.WBS1, LD.WBS2, LD.WBS3, LD.LaborCode

This seemed to work, however, because of the structure of our projects, a labor code may be used on various different phases (wbs1, wbs2, or wbs3) of the project. In running this, a vast majority of the labor codes were inserted in to LB, however there are several rows that were not inserted into the table because the laborcode occurs elsewhere in the table with a different WBS1, WBS2 and WBS3 combination.

A slightly different way of looking at this might explain this better:
Essentially WBS1, WBS2, WBS3 and the laborcode combined create a unique entry. These fields correspond to the following project structure: Project-Phase-Task-Laborcode

When I make my selection, I may get the same laborcode used 10 times with different combinations of values for WBS1, WBS2 and WBS3. I need to be able to ensure that the insert will add each of these combinations to the table even though they have the same laborcode.

Any suggestions on how to go about doing this.

Thanks for you help thus far!! I can't quite taste victory, but I can smell it. I know that it has to be somewhere close.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 14:19:57

I think you need to remove your WHERE condition as i believe thats what is filtering out only one combination onto LB table.

kkuhns
Starting Member

7 Posts

Posted - 2008-07-10 : 15:10:19

quote:
Originally posted by visakh16

I think you need to remove your WHERE condition as i believe thats what is filtering out only one combination onto LB table.

Which "where" are you refering to the 1st one or the 2nd.

kkuhns
Starting Member

7 Posts

Posted - 2008-07-10 : 15:23:09

I have tried removing the following:

and LD.laborcode not in (Select lb.laborcode from LB where lb.wbs1='31070003.000')

and I get the following error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'LBPK'. Cannot insert duplicate key in object 'dbo.LB'.
The statement has been terminated.

From looking at my LB Table specifically the LBPK, I need to insert all unique combinations of WBS1, WBS2, WBS3 and Laborcode from my select statement into the LB table other wise I will violate this Primary Key constraint.

At this point I am struggling at figuring out how to tell it to look at all 4 variables rather than just the one "laborcode"

kkuhns
Starting Member

7 Posts

Posted - 2008-07-10 : 17:17:14

visakh16 thanks for you help.

I finally got the Insert portion to work seamlessly (and much easier). Here is what I ended up using:

INSERT INTO LB (wbs1, wbs2, wbs3, laborcode)
SELECT distinct LD.WBS1 as wbs1, LD.WBS2 as wbs2, LD.WBS3 as wbs3, LD.LaborCode as laborcode
FROM LD
WHERE (WBS1 = '31070003.000') and (WBS1 + WBS2 + WBS3 + LaborCode) NOT IN
(SELECT WBS1 + WBS2 + WBS3 + LaborCode
FROM LB
WHERE (WBS1 = '31070003.000'))

By doing this I am not in violation of the Primary Key constraint 'LBPK' thank you for getting me going in the right direction.

Now I need to get back into the update portion of this process

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources