Update Query

Question

Post reply

Update Query

Thugs

SSC Enthusiast

Points: 141
More actions
February 9, 2015 at 5:55 am

#301009

I have 4 columns, ID, Dept.Number, Address and Grouping
ID Dept Address GroupID
165683 32 P O BOX 602 2
165925 32 PO BOX 562 2
165696 50 P O BOX 602 4
164356 103 PO BOX 175 6
164356 103 PO BOX 175 6
164368 203 PO BOX 72 19
165856 264 PO BOX 562 27
164374 302 PO BOX 39 32
164375 303 PO BOX 368 33
164375 303 PO BOX 368 33
164375 303 PO BOX 368 33
164375 303 PO BOX 368 33
165934 303 PO BOX 175 33
165934 303 PO BOX 175 33
164382 406 PO BOX 72 47
164421 905 PO BOX 39 57
165616 4310 PO BOX 368 87
165616 4310 PO BOX 368 87
165616 4310 PO BOX 368 87
165616 4310 PO BOX 368 87
166077 4913 ROUTE 3 89
165768 5434 PO BOX 368 91
164905 5434 PO BOX 368 91
165768 5434 PO BOX 368 91
164905 5434 PO BOX 368 91
165768 5434 PO BOX 368 91
164905 5434 PO BOX 368 91
164934 5803 PO BOX 368 92
164934 5803 PO BOX 368 92
164934 5803 PO BOX 368 92
164934 5803 PO BOX 368 92
166095 8040 ROUTE 3 109
165294 9311 PO BOX 175 115
165294 9311 PO BOX 175 115
I need to update the newgroupID field with the group ID based on the
Address and Dept columns.
First if address column matches update the newgruopID, With min GroupID Value.
Second, If the Address is diff and Dept is same update the new groupid with the group id of min Dept.
I have a hugs table and, I need to do it recursively.
Sample OUTPUT:
ID Dept Address GroupID New GroupID
165683 32 POBOX602 2 2
165925 32 POBOX562 2 2
165696 50 POBOX602 4 2
164356 103 POBOX175 6 6
164356 103 POBOX175 6 6
164368 203 POBOX72 19 19
165856 264 POBOX562 27 2
164374 302 POBOX39 32 32
164375 303 POBOX368 33 6
164375 303 POBOX368 33 6
164375 303 POBOX368 33 6
164375 303 POBOX368 33 6
165934 303 POBOX175 33 6
165934 303 POBOX175 33 6
164382 406 POBOX72 47 19
164421 905 POBOX39 57 32
165616 4310 POBOX368 87 6
165616 4310 POBOX368 87 6
165616 4310 POBOX368 87 6
165616 4310 POBOX368 87 6
166077 4913 ROUTE3 89 89
165768 5434 POBOX368 91 6
164905 5434 POBOX368 91 6
165768 5434 POBOX368 91 6
164905 5434 POBOX368 91 6
165768 5434 POBOX368 91 6
164905 5434 POBOX368 91 6
164934 5803 POBOX368 92 6
164934 5803 POBOX368 92 6
164934 5803 POBOX368 92 6
164934 5803 POBOX368 92 6
166095 8040 ROUTE3 109 89
165294 9311 POBOX175 115 6
165294 9311 POBOX175 115 6
Can anyone please pls help me on this.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

data in a consumable format:

i got tired of the solution just cleaning up the data, and gave up at that point.

;WITH MyCTE([ID],[Dept],[Address],[GroupID ])

AS

(

SELECT '165683','32 ','P O BOX 602','2' UNION ALL

SELECT '165925','32 ','PO BOX 562 ','2' UNION ALL

SELECT '165696','50 ','P O BOX 602','4' UNION ALL

SELECT '164356','103 ','PO BOX 175 ','6' UNION ALL

SELECT '164368','203 ','PO BOX 72 ','19' UNION ALL

SELECT '165856','264 ','PO BOX 562 ','27' UNION ALL

SELECT '164374','302 ','PO BOX 39 ','32' UNION ALL

SELECT '164375','303 ','PO BOX 368 ','33' UNION ALL

SELECT '165934','303 ','PO BOX 175 ','33' UNION ALL

SELECT '164382','406 ','PO BOX 72 ','47' UNION ALL

SELECT '164421','905 ','PO BOX 39 ','57' UNION ALL

SELECT '165616','4310','PO BOX 368 ','87' UNION ALL

SELECT '166077','4913','ROUTE 3 ','89' UNION ALL

SELECT '165768','5434','PO BOX 368 ','91' UNION ALL

SELECT '164905','5434','PO BOX 368 ','91' UNION ALL

SELECT '165768','5434','PO BOX 368 ','91' UNION ALL

SELECT '164905','5434','PO BOX 368 ','91' UNION ALL

SELECT '165768','5434','PO BOX 368 ','91' UNION ALL

SELECT '164905','5434','PO BOX 368 ','91' UNION ALL

SELECT '164934','5803','PO BOX 368 ','92' UNION ALL

SELECT '166095','8040','ROUTE 3 ','109' UNION ALL

SELECT '165294','9311','PO BOX 175 ','115' UNION ALL

SELECT '165294','9311','PO BOX 175 ','115'

)

SELECT * FROM MyCTE;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Make it easy for us to help you by creating tables, populating them with data (you could use Lowells work for that?) and then showing expected output from your description of what you need. Difficult to follow, especially since you say you need recursive activity to accomplish the end result.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service