Update one record from many records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update one record from many records

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-15 : 07:47:49

Hi,

I have a table with two account types, company and client, there are two companies in my example companya and comapnyb and they have client records that they are associated to. In the example the link between the company and the clients is comp_id. So companya that has the comp_id of 111 is related to the clients that also have a comp_id of 111 and the same link applies for companyb.

Ok the issue, I need to take the acct_no from just one of the client records and update the company records acct_no where the company comp_id = the client comp_id, because there are multiple client records per comp_id I can’t get an update query to take one of those client records from each different comp_id and update the company acct_no. I think and have been trying some code I found that looks something like SELECT ROW_NUMBER() OVER (PARTITION BY…. But I’m not sure I’m on the right track or if there is a simpler method I can use.

I hope that makes sense, it does not matter what client record is picked as clients associated to the company will all have the same comp_id.


CREATE TABLE TABLE1 (ID INT, account_type VARCHAR(10), comp_id VARCHAR(10), acct_no VARCHAR(10))
INSERT INTO TABLE1
SELECT 1, 'Companya', '111', ''
UNION ALL SELECT 2, 'client', '111', 'acc1'
UNION ALL SELECT 3, 'client', '111', 'acc1'
UNION ALL SELECT 4, 'Companyb', '222', ''
UNION ALL SELECT 5, 'client', '222', 'acc2'
UNION ALL SELECT 6, 'client', '222', 'acc2'
UNION ALL SELECT 7, 'client', '222', 'acc2'

My end result would ideally look like:

1, 'Companya', '111', 'acc1'
2, 'client', '111', 'acc1'
3, 'client', '111', 'acc1'
4, 'Companyb', '222', 'acc2'
5, 'client', '222', 'acc2'
6, 'client', '222', 'acc2'
7, 'client', '222', 'acc2'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-15 : 08:02:26

update table1
set acct_no = t2.acct_no
from table1 t
join (select comp_id, acct_no = max(acct_no) from table1 where account_type = 'client' group by comp_id) t2
on t.comp_id = t2.comp_id
where t.account_type <> 'Client'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-15 : 08:19:05

Thanks for the quick response. That almost worked but it updated some of the client records acct_no too and where it did, it updated them to the wrong value of the opposite companies account number. Maybe my explanation was not so good, do you see what I am trying to achieve now? Thanks again

Here is what that query has done

1 Company 111 acc1
2 client 111 acc2
3 client 111 acc1
4 Company 222 acc2
5 client 222 acc1
6 client 222 acc2
7 client 222 acc1

this is the result i wanted

1 Company 111 acc1
2 client 111 acc1
3 client 111 acc1
4 Company 222 acc2
5 client 222 acc2
6 client 222 acc2
7 client 222 acc2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-15 : 08:27:31

Do you have different set of data? The above code would work fine

Madhivanan

Failing to plan is Planning to fail

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-15 : 08:38:15

Hi,

I used


CREATE TABLE TABLE1 (ID INT, account_type VARCHAR(10), comp_id VARCHAR(10), acct_no VARCHAR(10))
INSERT INTO TABLE1
SELECT 1, 'Companya', '111', ''
UNION ALL SELECT 2, 'client', '111', 'acc1'
UNION ALL SELECT 3, 'client', '111', 'acc1'
UNION ALL SELECT 4, 'Companyb', '222', ''
UNION ALL SELECT 5, 'client', '222', 'acc2'
UNION ALL SELECT 6, 'client', '222', 'acc2'
UNION ALL SELECT 7, 'client', '222', 'acc2'

Then


update table1
set acct_no = t2.acct_no 
from table1 t
join (select comp_id, acct_no = max(acct_no) from table1 where account_type = 'client' group by comp_id) t2
on t.comp_id = t2.comp_id
where t.account_type <> 'Client'

And the results i get are:

1 Company 111 acc1
2 client 111 acc2
3 client 111 acc1
4 Company 222 acc2
5 client 222 acc1
6 client 222 acc2
7 client 222 acc1

Can you check to see if you get different results? I can't see that i am doing anything wrong.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-15 : 08:56:14

Case sensitive?

update table1
set acct_no = t2.acct_no
from table1 t
join (select comp_id, acct_no = max(acct_no) from table1 where account_type = 'client' group by comp_id) t2
on t.comp_id = t2.comp_id
where t.account_type <> 'client'

But I don't see how that can account for what you have posted.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-15 : 08:58:01

try this

declare @TABLE1 table (ID INT, account_type VARCHAR(10), comp_id VARCHAR(10), acct_no VARCHAR(10))
INSERT INTO @TABLE1
SELECT 1, 'Companya', '111', ''
UNION ALL SELECT 2, 'client', '111', 'acc1'
UNION ALL SELECT 3, 'client', '111', 'acc1'
UNION ALL SELECT 4, 'Companyb', '222', ''
UNION ALL SELECT 5, 'client', '222', 'acc2'
UNION ALL SELECT 6, 'client', '222', 'acc2'
UNION ALL SELECT 7, 'client', '222', 'acc2'

select * from @TABLE1 order by id

update @table1
set acct_no = t2.acct_no
from @table1 t
join (select comp_id, acct_no = max(acct_no) from @table1 where account_type = 'client' group by comp_id) t2
on t.comp_id = t2.comp_id
where t.account_type <> 'client'

select * from @TABLE1 order by id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-15 : 09:10:33

This is crazy, the last example you sent works fine but if i create the table then run the following I get the bad results. Not sure what do because I need to run this query on a big table and I won't be able to check through it all manually.

I'll keep playing with it, it's got to be something silly. Thanks for the help.


update table1
set acct_no = t2.acct_no 
from table1 t
join (select comp_id, acct_no = max(acct_no) from table1 where account_type = 'client' group by comp_id) t2
on t.comp_id = t2.comp_id
where t.account_type <> 'client'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources