update script - 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

SQL Server 2005 Forums

Transact-SQL (2005)

update script

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-06-03 : 09:29:31

Hi,

I have table structures and example data like below.
How do i update Col3 where Col2 is N and Col3 is NULL, my conditions are : Get Col3's value where Col2 is Y and Update Col3 until Col2 not equal to D value. Please see output result.

Declare @tbl1 table
(Col1 INT,
Col2 varchar (50),
Col3 varchar(50)
)

INSERT INTO @tbl1
SELECT 1, 'Y','ABC1001'

INSERT INTO @tbl1
SELECT 2, 'N',NULL

INSERT INTO @tbl1
SELECT 3, 'N',NULL

INSERT INTO @tbl1
SELECT 4, 'N',NULL

INSERT INTO @tbl1
SELECT 5, 'Y','ABC1002'

INSERT INTO @tbl1
SELECT 6, 'N',NULL

INSERT INTO @tbl1
SELECT 7, 'Y','ABC1003'

INSERT INTO @tbl1
SELECT 8, 'Y','ABC1004'

SELECT * FROM @tbl1

Output looks like this:

Col1 Col2 Col3
1 Y ABC1001
2 N ABC1001
3 N ABC1001
4 N ABC1001
5 Y ABC1002
6 N ABC1002
7 Y ABC1003
8 Y ABC1004

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 09:38:18

select t.col1, t.col2,
col3 = case when t.col2 = 'Y' then t.col3 else (select top 1 col3 from @tbl1 t2 where t2.col1 <= t.col1 and t2.col3 is not null) end
from @tbl1 t

==========================================
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.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-06-03 : 10:03:43

nigelrivett, please rows 5 and 6 this has to be ABC1002.

i added more data, please see.

Declare @tbl1 table
(Col1 INT,
Col2 varchar (50),
Col3 varchar(50)
)

INSERT INTO @tbl1
SELECT 1, 'Y','ABC1001'

INSERT INTO @tbl1
SELECT 2, 'N',NULL

INSERT INTO @tbl1
SELECT 3, 'N',NULL

INSERT INTO @tbl1
SELECT 4, 'N',NULL

INSERT INTO @tbl1
SELECT 5, 'Y','ABC1002'

INSERT INTO @tbl1
SELECT 6, 'N',NULL

INSERT INTO @tbl1
SELECT 7, 'Y','ABC1003'

INSERT INTO @tbl1
SELECT 8, 'Y','ABC1004'

INSERT INTO @tbl1
SELECT 9, 'Y','ABC1005'

INSERT INTO @tbl1
SELECT 10, 'N',NULL

INSERT INTO @tbl1
SELECT 11, 'N',NULL

INSERT INTO @tbl1
SELECT 12, 'Y','ABC1006'

INSERT INTO @tbl1
SELECT 13, 'N',NULL

INSERT INTO @tbl1
SELECT 14, 'Y','ABC1007'

INSERT INTO @tbl1
SELECT 15, 'N',NULL

select t.col1, t.col2,
col3 = case when t.col2 = 'Y' then t.col3 else (select top 1 col3 from @tbl1 t2 where t2.col1 <= t.col1 and t2.col3 is not null) end
from @tbl1 t

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-03 : 10:14:47

oops
select t.col1, t.col2,
col3 = case when t.col2 = 'Y' then t.col3 else (select top 1 t2.col3 from @tbl1 t2 where t2.col1 <= t.col1 and t2.col3 is not null order by t2.col1 desc) end
from @tbl1 t

==========================================
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.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-06-03 : 11:38:19

thanks nigelrivett, great job.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources