Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

T SQL Update Query

I am trying to write a TSQL stored procedure to handle a change to a database record.

My main table is icomponent which has columns of

ipart_id
ilocation_id
icomponent_id
icomponent_count

The table contains a number of component records where each component has a parent part (ipart_id) and a location (ilocation_id) and an instance count (icomponent_count)

What I am trying to achieve is a 'merge' of components so that instead of having say 4 instances of a part I have only 1 instance with a count of 4 (assuming all instances are in the same location).

To this end I need a query which will look through the icomponent table and produce a count for each part by location.  Results would be

PART_ID    LOCATION_ID   COUNT
1                       5                     2
1                       6                     1
1                       7                     4

I then need to update the first instance of the component in each location so that it's count field is set to the COUNT returned above and finally all of the other components need to be deleted.

As an example if my table looked like:-

icomponent_id        ipart_id          ilocation_id    icomponent_count
     1                            1                        5                        1
     2                            1                        5                        1
     3                            1                        6                        1
     4                            1                        6                        1
     5                            1                        6                        1
     6                            1                        7                        1
     7                            1                        7                        1

When the query completes the table would now like like :-

icomponent_id        ipart_id          ilocation_id    icomponent_count
     1                            1                        5                        2
     3                            1                        6                        3
     6                            1                        7                        2

Any help with this query would be most gratefully received...  An explanation as to what the query is doing would help also as I keep finding that I cannot get past the more simple queries when I have multi-part queries to create!
0
ChrisMDrew
Asked:
ChrisMDrew
  • 2
  • 2
1 Solution
 
JoeNuvoCommented:
If there is no other fields, maybe delete whole and re-insert with only summary record maybe easier

Declare @Temp TABLE (ipart_id int, ilocation_id int, mn int, cnt int)
INSERT INTO @Temp
SELECT ipart_id, ilocation_id, MIN(icomponent_id) mn, SUM(icomponent_count) cnt
FROM icomponent
GROUP BY ipart_id, ilocation_id

DELETE FROM icomponent

INSERT INTO icomponent (ipart_id, ilocation_id, icomponent_id, icomponent_count)
SELECT * FROM @Temp

Open in new window

0
 
ChrisMDrewAuthor Commented:
Unfortunately that would be difficult to implement as there are a load more columns in the component table and I would have to copy all of these into the temp table.  Also the icomponent_id is an identity field which would cause problems as tthe ID of the component would change.  I may have to do this longhand as thinking about it I can't just delete the additional components as there are multiple tables with foreign keys to the icomponent table.
0
 
JoeNuvoCommented:
regardless of other related table.

here is new code base on your guide "icomponent_id is an identity"

-- get list of component count per part/location with min of ID
Declare @Temp TABLE (icomponent_id int PRIMARY KEY, 
	ipart_id int, ilocation_id int, icomponent_count int)
INSERT INTO @Temp
SELECT MIN(icomponent_id), ipart_id, ilocation_id, SUM(icomponent_count)
FROM icomponent
GROUP BY ipart_id, ilocation_id

-- delete unneed record
DELETE I
FROM icomponent I
WHERE NOT EXISTS (SELECT icomponent_id
FROM @Temp T
WHERE I.icomponent_id = T.icomponent_id)

-- update "sum" of count value back into table
UPDATE icomponent
SET icomponent_count = T.icomponent_count
FROM @Temp T
WHERE icomponent.icomponent_id = T.icomponent_id

Open in new window

0
 
ChrisMDrewAuthor Commented:
Thanks - just what I need - I can tweak this to handle dependancies
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now