update records - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

update records

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-08-03 : 09:18:01

Hi all

I am not able to put the logic how to update the agent value for the respective acounts

invoice account agent
11 1 vijay
12 1 vikas
13 2 ajay
14 2 vijay
15 2 vijay

I want updated table in this manner

invoice account agent
11 1 vijay
12 1 vijay
13 2 ajay
14 2 ajay
15 2 ajay

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-03 : 10:39:21

What is the logic to do the update? Should we choose the agent for the lesser invoice number for each account?

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-04 : 01:54:56

i think the logic must be like

 where invoice in('14','15')

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-04 : 02:19:47

[code]UPDATE table_name
SET agent=ajay
WHERE invoice in('14','15')[/code]
try this

With Regards
Kashyap M

Sachin.Nand

2937 Posts

Posted - 2010-08-04 : 06:38:54

[code]
update t1 set t1.agent=t.agent
from yourtable T1
cross apply(select top 1 agent from yourtable t2 where t1.account=t2.account)T
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources