how to 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

General SQL Server Forums

New to SQL Server Programming

how to update

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-03 : 15:49:55

I have a data like this:

ID Col1 Col2
1
2 1
3
4 3
5 3

I want to update Col2 like this:
Id Col1 Col2
1 1
2 1 1
3 3
4 3 3
5 3 3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-03 : 16:01:07

Could you describe in words how to derive that?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-03-03 : 16:02:09

Hi The following will give you what you want .I've assumed your columns are data type = INT , and NULL would be the default value . Obviously , you can change the code slightly.

create table #x
(ID INT,
COL1 INT,
COL2 INT
)

INSERT INTO #x
SELECT 1, NULL,NULL

INSERT INTO #x
SELECT 2, 1,NULL

INSERT INTO #x
SELECT 3, NULL,NULL

INSERT INTO #x
SELECT 4, 3,NULL

INSERT INTO #x
SELECT 5,3 ,NULL

SELECT * FROM #x

UPDATE #x SET COL1 = CASE
WHEN ISNULL(COL1,0) < 1 THEN ID
ELSE COL1
END,
COL2 = CASE
WHEN ISNULL(COL2,0) < 1 THEN COL1
ELSE COL2
END
SELECT * FROM #x
DROP TABLE #x

Jack Vamvas
--------------------
http://www.ITjobfeed.com

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-03 : 16:13:17

jackv,

I have to update only col2
the following recrods are just update ID 1 and ID 3

OUT PUT Will be
ID Col2
1 1
3 3
create table #x
(ID INT,
COL1 INT,
COL2 INT
)

INSERT INTO #x
SELECT 1, NULL,NULL

INSERT INTO #x
SELECT 2, 1,NULL

INSERT INTO #x
SELECT 3, NULL,NULL

INSERT INTO #x
SELECT 4, 3,NULL

INSERT INTO #x
SELECT 5,3 ,NULL

INSERT INTO #x
SELECT 6,NULL ,NULL

INSERT INTO #x
SELECT 7,NULL ,NULL

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-03-03 : 16:18:53

rudba
If you want to update only COL2 do :
UPDATE #x SET COL2 = CASE
WHEN ISNULL(COL2,0) < 1 THEN COL1
ELSE COL2
END

Is that what you are looking for?,because the way you showed the data initially , implied that both columns are updated

Jack Vamvas
--------------------
http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 08:53:06

quote:
Originally posted by rudba

I have a data like this:

ID Col1 Col2
1
2 1
3
4 3
5 3

I want to update Col2 like this:
Id Col1 Col2
1 1
2 1 1
3 3
4 3 3
5 3 3

seems like what you need is


UPDATE t
SET t.Col2=(SELECT TOP 1 Col2 FROM Table WHERE ID < t.ID ORDER BY ID DESC)
FROM Table t
WHERE NULLIF(t.Col2,'') IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources