Solved

T SQL Update Query

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now