Solved

T SQL Update Query

Posted on 2011-03-18
4
257 Views
Last Modified: 2012-05-11
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
Comment
Question by:ChrisMDrew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35164776
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
 

Author Comment

by:ChrisMDrew
ID: 35165750
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
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 35166234
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
 

Author Closing Comment

by:ChrisMDrew
ID: 35166414
Thanks - just what I need - I can tweak this to handle dependancies
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question