Solved

T SQL Update Query

Posted on 2011-03-18
4
252 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks - just what I need - I can tweak this to handle dependancies
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

12 Experts available now in Live!

Get 1:1 Help Now