SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Need Help for Row Update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HardHabit
Starting Member

12 Posts

Posted - 05/13/2008 :  01:36:27  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/13/2008 :  02:03:11  Show Profile  Reply with Quote
Do you have a primary key column in your table?
Go to Top of Page

HardHabit
Starting Member

12 Posts

Posted - 05/13/2008 :  02:20:33  Show Profile  Reply with Quote
the only primary key that i have is the number that appear on my sample data
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30101 Posts

Posted - 05/13/2008 :  02:56:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/13/2008 :  03:34:13  Show Profile  Reply with Quote
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
Go to Top of Page

HardHabit
Starting Member

12 Posts

Posted - 05/13/2008 :  04:29:59  Show Profile  Reply with Quote
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....







Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/13/2008 :  04:30:57  Show Profile  Reply with Quote
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?
Go to Top of Page

HardHabit
Starting Member

12 Posts

Posted - 05/13/2008 :  04:42:08  Show Profile  Reply with Quote
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...


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30101 Posts

Posted - 05/13/2008 :  05:24:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/13/2008 :  06:45:05  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30101 Posts

Posted - 05/13/2008 :  06:49:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 05/13/2008 06:51:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/13/2008 :  11:10:30  Show Profile  Reply with Quote
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
Go to Top of Page

HardHabit
Starting Member

12 Posts

Posted - 05/13/2008 :  21:19:40  Show Profile  Reply with Quote
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.....

Go to Top of Page

chandan_joshi80
Starting Member

India
30 Posts

Posted - 05/14/2008 :  03:29:17  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30101 Posts

Posted - 05/14/2008 :  03:41:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Which is different from what I posted 05/13/2008 : 05:24:53 ?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/14/2008 :  04:05:46  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000