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

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

---

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

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

quote:

---

Originally posted by visakh16

works for me too:-

---

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

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"

---

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"

---