Solved

T SQL Update Query

Posted on 2011-03-18
4
258 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 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