Update query - 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 query

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-25 : 20:03:14

Hi,

I have a table called ClientTbl. I need to update the parentid column in this table. The parent id is the value in the Id column where the Div = 00 for the specific client. For Div = 00 the parent should be NULL.

Is it possible to wite an update statement to do this or should i cursor through each records and update the parent id.

ClientTable

Id Client Div Parent
1 00003 00 NULL
2 00003 50 NULL
3 00003 99 NULL
4 00004 00 NULL
5 00005 00 NULL
6 00022 00 NULL
7 00025 00 NULL
8 00025 01 NULL
9 00025 02 NULL

Output should be like this.

Id Client Div Parent
1 00003 00 NULL
2 00003 50 1
3 00003 99 1
4 00004 00 NULL
5 00005 00 NULL
6 00022 00 NULL
7 00025 00 NULL
8 00025 01 7
9 00025 02 7

Please advise..
Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-25 : 20:18:15

[code]
update ClientTable
set
Parent = a.ID
from
ClientTable
join
ClientTable a
on ClientTable.Client = a.Client
where
a.Client = '00'

[/code]

CODO ERGO SUM

yumyum113
Starting Member

31 Posts

Posted - 2007-04-25 : 20:33:27

Hi pvccaz,

Hope this helps...

declare @temptable table (
ID int,
Client int,
Div int,
Parent int)

insert into @temptable
select 1, 00003, 00, NULL union all
select 2, 00003, 50, NULL union all
select 3, 00003, 99, NULL union all
select 4, 00004, 00, NULL union all
select 5, 00005, 00, NULL union all
select 6, 00022, 00, NULL union all
select 7, 00025, 00, NULL union all
select 8, 00025, 01, NULL union all
select 9, 00025, 02, NULL

update @temptable
set parent = t2.id
from @temptable t1
left join (
select client,min(id) 'id'
from @temptable
group by client
having count(client)>1) t2 on t1.client = t2.client and div<>0

select * from @temptable

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-25 : 20:36:22

I tried but no rows are getting updated.

(0 row(s) affected)

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-25 : 20:41:28

Hi yumyum113

It works.. Thank you...!!

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-26 : 00:11:27

Try this is simple
declare @tt table (
ID int,
Client int,
Div int,
Parent int)

insert into @tt
select 1, 00003, 00, NULL union all
select 2, 00003, 50, NULL union all
select 3, 00003, 99, NULL union all
select 4, 00004, 00, NULL union all
select 5, 00005, 00, NULL union all
select 6, 00022, 00, NULL union all
select 7, 00025, 00, NULL union all
select 8, 00025, 01, NULL union all
select 9, 00025, 02, NULL

Update t
Set parent = a.id
from @tt t, @tt a
where t.client = a.client and t.div<>0

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-26 : 14:21:08

Hi pbguy,
Thanks so much.. It works fine..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources