update multiple rows in a table.

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 2000 Forums

SQL Server Development (2000)

update multiple rows in a table.

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-10-24 : 17:04:48

Hi,

I have following table. I should update the datethru column like the following. Any help is appreciated.

id part_num datefrom datethru
1 ABCD 10-01-2006 NULL
2 ABCD 10-03-2006 NULL
3 ABCD 10-05-2006 NULL
4 PQRS 10-05-2006 NULL
5 IJKL 10-03-2006 NULL
6 IJKL 10-05-2006 NULL

id part_num datefrom datethru
1 ABCD 10-01-2006 10-03-2006
2 ABCD 10-03-2006 10-05-2006
3 ABCD 10-05-2006 12-31-2099
4 PQRS 10-05-2006 12-31-2099
5 IJKL 10-03-2006 10-05-2006
6 IJKL 10-05-2006 12-31-2099

thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-24 : 17:23:15

You need to write a standard UPDATE statement (see Books Online). You'll probably need more than one statement because it looks like you have lots of different updates you want to perform, but it could be something like

UPDATE yourtable
SET datethru = '10/03/2006'
WHERE id = 1

If you haev rules that apply to many rows then change the WHERE clause to apply to all the rows, that example will update just one row, but something like this will update multiple rows

UPDATE yourtable
SET datethru = '12/31/2099'
WHERE datefrom = '10/5/2006'

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-10-24 : 18:26:03

Sorry this just a example. I have mutilpe rows like this. So I need general rule to group by the partnum and then update the datefrom and datethru accordingly.

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-24 : 18:31:49

[code]
declare @table table
(
id int,
part_num varchar(10),
datefrom datetime,
datethru datetime
)
insert into @table
select 1, 'ABCD', '10-01-2006', NULL union all
select 2, 'ABCD', '10-03-2006', NULL union all
select 3, 'ABCD', '10-05-2006', NULL union all
select 4, 'PQRS', '10-05-2006', NULL union all
select 5, 'IJKL', '10-03-2006', NULL union all
select 6, 'IJKL', '10-05-2006', NULL

update t
set datethru = coalesce(s.datefrom, '20991231')
from @table t left join @table s
on t.part_num = s.part_num
and s.datefrom = (select min(datefrom) from @table x where x.part_num = s.part_num and x.datefrom > t.datefrom)

select * from @table
/*
id part_num datefrom datethru
----------- ---------- ----------- -----------
1 ABCD 2006-10-01 2006-10-03
2 ABCD 2006-10-03 2006-10-05
3 ABCD 2006-10-05 2099-12-31
4 PQRS 2006-10-05 2099-12-31
5 IJKL 2006-10-03 2006-10-05
6 IJKL 2006-10-05 2099-12-31
*/
[/code]

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-10-25 : 13:08:27

Thank you soooooooooooo much. You made my day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources