Insert Value into a NULL column

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)

Insert Value into a NULL column

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-10-03 : 12:09:54

I'm new to T-SQL programming and was hoping for some help.

I have a table SQL 2000 Table that contains data looking like this;

PK,Spec,Date,Time
1,AE,20/05/2007,900
2,,21/05/2007,900
3,,22/05/2007,900
4,ORTH,20/05/2007,855
5,,21/05/2007,855
6,OPTH,18/06/2007,905
7,,19/06/2007,910

I would like the build a statement where the Spec column is updated to reflect the value of the Spec. So as it looks like this...

PK,Spec,Date,Time
1,AE,20/05/2007,900
2,AE,21/05/2007,900
3,AE,22/05/2007,900
4,ORTH,20/05/2007,855
5,ORTH,21/05/2007,855
6,ENT,18/06/2007,905
7,ENT,19/06/2007,910

Is there a way to achieve this.

Any help would be most appreciated.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 12:14:10

UPDATE t1
SET t1.Spec = (SELECT TOP 1 t2.Spec FROM Table1 AS t2 WHERE t2.Spec IS NOT NULL AND t2.PK < t1.PK ORDER BY t2.PK DESC)
FROM Table1 AS t1
WHERE t1.Spec IS NULL

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

sshelper
Posting Yak Master

216 Posts

Posted - 2007-10-03 : 12:15:03

Using the Time column as the common value for the Spec, you can try the following UPDATE statement:

UPDATE A
SET [Spec] = B.[Spec]
FROM YourTable A INNER JOIN YourTable B
ON A.[Time] = B.[Time] AND A.[Spec] = '' AND B.[Spec] != ''

SQL Server Helper
http://www.sql-server-helper.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 12:17:57

Won't work for line 6 & 7.

-- Prepare sample data
DECLARE @Sample TABLE (PK INT, Spec VARCHAR(4), Date DATETIME, Time INT)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	1, 'AE',   '20/05/2007', 900 UNION ALL
SELECT	2, NULL,   '21/05/2007', 900 UNION ALL
SELECT	3, NULL,   '22/05/2007', 900 UNION ALL
SELECT	4, 'ORTH', '20/05/2007', 855 UNION ALL
SELECT	5, NULL,   '21/05/2007', 855 UNION ALL
SELECT	6, 'OPTH', '18/06/2007', 905 UNION ALL
SELECT	7, NULL,   '19/06/2007', 910

-- Do the update
UPDATE	t1
SET	t1.Spec = (SELECT TOP 1 t2.Spec FROM @Sample AS t2 WHERE t2.Spec IS NOT NULL AND t2.PK < t1.PK ORDER BY t2.PK DESC)
FROM	@Sample AS t1
WHERE	t1.Spec IS NULL

-- Show the expected output
SELECT * FROM @Sample

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

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-10-03 : 12:52:55

Worked exactly as I’d wished.
Thanks for all your help - esp. PESO!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources