Help with this UPDATE

Question

Post reply

Help with this UPDATE

xRafo

SSChasing Mays

Points: 623
More actions
January 5, 2012 at 8:40 am

#253000

Hi All, i have this data:
DECLARE @spotresource TABLE (
id_resource int,id_offer int ,cod_resource int ,id_item int ,desc_resource varchar(500),man_hours int
)
INSERT INTO @spotresource
SELECT 1085404,1711,306224,39747,'Luminaria de 250W para alumbrado exterior - facilidades',7683 UNION ALL
SELECT 1085405,1711,306225,39747,'Luminaria de 70W para alumbrado exterior - viaducto',338 UNION ALL
SELECT 1085495,1711,306182,39747,'Tuber¡a para alumbrado Exterior - Viaducto',594
SELECT * FROM @spotresource
DECLARE @spotCM_cc46311AREP TABLE (
id_resource int,man_hours_en int ,man_hours_en_con int
)
INSERT INTO @spotCM_cc46311AREP
SELECT 1085404,12468,12468 UNION ALL
SELECT 1085405,7624,7624 UNION ALL
SELECT 1085495,2726,2726
SELECT * FROM @spotCM_cc46311AREP
as you can see there is some common columns which are man-hours, id_resource, I have to do is compare each record by the same resource_id and verify that the man-hours are equal; if not, update the table "spotCM_cc46311AREP" and to place the man-hours I should have in the table spotresource
Example:
In the table @spotresource i have this id_resource: 1085404
and the man_hours are:7683
but in the another table spotCM_cc46311AREP
the value of man_hours of the same id_resource is 12468
how can i update the value of 12468 ?
I hope have unterstand me
and sorry for my bad english
____________________________________________________________________________
Rafo*

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Your english was fine and you did a great job posting details so we can answer your question.

If I understand your issue, something like this should work:

update @spotCM_cc46311AREP

set man_Hours_en = ManHours

from

(

select sum(man_hours) as ManHours, id_offer, id_resource from @spotresource

--where id_offer=1711

--and id_resource=1085404

group by id_offer, id_resource

) total

join @spotCM_cc46311AREP s on s.id_resource = total.id_resource

If you want to update only the row for a specific id_offer and id_resource you can uncomment the where clause. Hope that is what you are looking for.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

xRafo SSChasing Mays Points: 623 More actions · Answer 2

Hey thanks!

and if i want scan all the man_hours by id_resource of the table @spotresource and compare it with the table @spotCM_cc46311AREP how can i update the man_hours for each record (by id_resource ) ??

____________________________________________________________________________
Rafo*

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

That is actually what I posted. I assumed that was what you were after. Notice in the subquery that the specific values are commented out.