Need Help for Row Update - 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 2000 Forums

Transact-SQL (2000)

Need Help for Row Update

Author

Topic

HardHabit
Starting Member

12 Posts

Posted - 2008-05-13 : 01:36:27

Hi, i need help please

I have this table

1.AAA
2.AAB
3.NULL
4.NULL
5.AAC
6.NULL
7.AAD

I want to get the output like this...

1.AAA
2.AAB
3.AAB
4.AAB
5.AAC
6.AAC
7.AAD

Any help would be highly appreciated...

Thanks in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 02:03:11

Do you have a primary key column in your table?

HardHabit
Starting Member

12 Posts

Posted - 2008-05-13 : 02:20:33

the only primary key that i have is the number that appear on my sample data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 02:56:04

Something similar to

UPDATE t1
SET t1.Col2 = (select top 1 x.col2 from table1 as x where x.col1 < t1.col1 and x.col2 is not null order by x.col1 desc)
FROM Table1 AS t1

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 03:34:13

or:-

DECLARE @CharVal varchar(20)

UPDATE t2
SET @CharVal=t2.col2=COALESCE(t1.col2,@CharVal)
FROM Table1 t1
INNER JOIN Table1 t2
ON t2.col1=t1.col1+1
AND  t2.col2 IS NULL

HardHabit
Starting Member

12 Posts

Posted - 2008-05-13 : 04:29:59

Thanks Peso for your reply! I try your code and here is the result

Process Data using the code

UPDATE t1
SET t1.Col2 = (select top 1 x.col2 from table1 as x where x.col1 < t1.col1 and x.col2 is not null order by x.col1 desc)
FROM Table1 AS t1

---Result -------
ID Name
1 NULL
2 AAA
3 AAB
4 NULL
5 NULL
6 AAC
7 NULL

ORIGINAL DATA
ID Name
1 AAA
2 AAB
3 NULL
4 NULL
5 AAC
6 NULL
7 AAD
-----------

UPDATE t1
SET t1.Col2 = (select top 1 x.col2 from table1 as x where x.col1 < t1.col1 and x.col2 is not null order by x.col1 desc)
FROM Table1 AS t1
WHERE t1.Col2 IS NULL --------I add this one just to update only null values..

the result is this...

[ID] NAME
1 AAA
2 AAB
3 AAB
4 NULL --- i want to remove this and replace with AAB
5 AAC
6 AAC
7 AAD

***there is a scenario that plenty of null appear on my data

Any idea about this...

Thanks in advance....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 04:30:57

quote:
Originally posted by HardHabit

Thanks Peso for your reply! I try your code and here is the result

Process Data using the code

UPDATE t1
SET t1.Col2 = (select top 1 x.col2 from table1 as x where x.col1 < t1.col1 and x.col2 is not null order by x.col1 desc)
FROM Table1 AS t1

---Result -------
ID Name
1 NULL
2 AAA
3 AAB
4 NULL
5 NULL
6 AAC
7 NULL

ORIGINAL DATA
ID Name
1 AAA
2 AAB
3 NULL
4 NULL
5 AAC
6 NULL
7 AAD
-----------

UPDATE t1
SET t1.Col2 = (select top 1 x.col2 from table1 as x where x.col1 < t1.col1 and x.col2 is not null order by x.col1 desc)
FROM Table1 AS t1
WHERE t1.Col2 IS NULL --------I add this one just to update only null values..

the result is this...

[ID] NAME
1 AAA
2 AAB
3 AAB
4 NULL --- i want to remove this and replace with AAB
5 AAC
6 AAC
7 AAD

***there is a scenario that plenty of null appear on my data

Any idea about this...

Thanks in advance....

Did you try mine?

HardHabit
Starting Member

12 Posts

Posted - 2008-05-13 : 04:42:08

yes visakh16 i'll also try your code..thanks also to you...
the result is this...

ID NAME
1 AAA
2 AAB
3 AAB
4 NULL --null is still there...
5 AAC
6 AAC
7 AAD

any thoughts of this...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 05:24:53

Works for me

DECLARE	@Sample TABLE (ID INT, Name VARCHAR(3))

INSERT	@Sample
SELECT	1, 'AAA' UNION ALL
SELECT	2, 'AAB' UNION ALL
SELECT	3, NULL UNION ALL
SELECT	4, NULL UNION ALL
SELECT	5, 'AAC' UNION ALL
SELECT	6, NULL UNION ALL
SELECT	7, 'AAD'

SELECT	*
FROM	@Sample

UPDATE	s1
SET	s1.Name = (SELECT TOP 1 x.Name FROM @Sample AS x WHERE x.ID < s1.ID AND x.Name IS NOT NULL ORDER BY x.ID DESC)
FROM	@Sample AS s1
WHERE	s1.Name IS NULL

SELECT	*
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 06:45:05

works for me too:-

DECLARE @temp table
(
ID int,
ChrVal varchar(10)
)

INSERT INTO @Temp
SELECT 1,'AAA'
UNION ALL
SELECT 2,'AAB'
UNION ALL
SELECT 3,NULL
UNION ALL
SELECT 4,NULL
UNION ALL
SELECT 5,'AAC'
UNION ALL
SELECT 6,NULL
UNION ALL
SELECT 7,'AAD'


select 'entered'
select * FROM @Temp 

DECLARE @CharVal varchar(20)

UPDATE t2
SET @CharVal=t2.ChrVal=COALESCE(t1.ChrVal,@CharVal)
FROM @Temp t1
INNER JOIN @Temp t2
ON t2.ID =t1.ID+1
AND  t2.ChrVal IS NULL

select 'result'
select * FROM @Temp 

output
------------------------------------------------
entered

ID          ChrVal
----------- ----------
1           AAA
2           AAB
3           NULL
4           NULL
5           AAC
6           NULL
7           AAD


------
result

ID          ChrVal
----------- ----------
1           AAA
2           AAB
3           AAB
4           AAB
5           AAC
6           AAC
7           AAD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 06:49:30

quote:
Originally posted by visakh16

works for me too:-

Yes it does.
This is a clever approach.
However it relies on a sequential number if ID.

This is a rewritten suggestion based on yours, but this relies on a present clustered index only.

DECLARE	@Sample TABLE (ID INT PRIMARY KEY CLUSTERED, Name VARCHAR(3))

INSERT	@Sample
SELECT	1, 'AAA' UNION ALL
SELECT	2, 'AAB' UNION ALL
SELECT	3, NULL UNION ALL
SELECT	4, NULL UNION ALL
SELECT	5, 'AAC' UNION ALL
SELECT	6, NULL UNION ALL
SELECT	7, 'AAD'

SELECT	*
FROM	@Sample

DECLARE	@Name VARCHAR(3)

UPDATE	@Sample
SET	@Name = Name = COALESCE(Name, @Name)

SELECT	*
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 11:10:30

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

works for me too:-
Yes it does.
This is a clever approach.
However it relies on a sequential number if ID.

This is a rewritten suggestion based on yours, but this relies on a present clustered index only.
DECLARE @Sample TABLE (ID INT PRIMARY KEY CLUSTERED, Name VARCHAR(3))

INSERT @Sample
SELECT 1, 'AAA' UNION ALL
SELECT 2, 'AAB' UNION ALL
SELECT 3, NULL UNION ALL
SELECT 4, NULL UNION ALL
SELECT 5, 'AAC' UNION ALL
SELECT 6, NULL UNION ALL
SELECT 7, 'AAD'

SELECT *
FROM @Sample

DECLARE @Name VARCHAR(3)

UPDATE @Sample
SET @Name = Name = COALESCE(Name, @Name)

SELECT *
FROM @Sample

E 12°55'05.25"
N 56°04'39.16"

Looks much better

HardHabit
Starting Member

12 Posts

Posted - 2008-05-13 : 21:19:40

Thanks Peso & visakh16 for all your efforts to solve my problem...you make my job easily....Thanks to both of you.....as for the code that you gave it to me..ill just add the WHILE loop to find any null values....and then its done!!!!!...and solve my problem!!!

Thanks again.....and its a pleasure to meet you both.....

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-05-14 : 03:29:17

declare @t table ( COL1 int, COL2 VARCHAR(3))
insert @t
select 1, 'AAA'
union all select 2, 'AAB'
union all select 3, NULL
union all select 4,NULL
union all select 5,'AAC'
union all select 6,NULL
union all select 7,'AAD'
SELECT * FROM @t

update @t set col2=(select top 1 x.col2 from @t as x where x.col1 < y.col1 and x.col2 is not null order by x.col1 desc)
from @t AS y
where COL2 is null

select * from @t

chandan Joshi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 03:41:17

Which is different from what I posted 05/13/2008 : 05:24:53 ?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 04:05:46

quote:
Originally posted by Peso

Which is different from what I posted 05/13/2008 : 05:24:53 ?

E 12°55'05.25"
N 56°04'39.16"

It seems like people wont read through all posts before giving their suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources