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 2005 Forums

Transact-SQL (2005)

update

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-18 : 15:24:36

Hello,

How do i update field2, take a look output.

declare @tbl1 table
(ID INT,
Field1 varchar(20),
Field2 varchar(10)
)

INSERT INTO @tbl1
SELECT 1, 1,'Abc'

INSERT INTO @tbl1
SELECT 2,1,null

INSERT INTO @tbl1
SELECT 3,3,'Mno'

INSERT INTO @tbl1
SELECT 4,1,null

INSERT INTO @tbl1
SELECT 5,3,null

Output Like this :
1 1 'Abc'
2 1 'Abc'
3 3 'Mno'
4 1 'Abc'
5 3 'Mno'

cblythe
Starting Member

6 Posts

Posted - 2010-05-18 : 15:47:12

I'm not sure what you are asking, when I execute this I get:

1	1	Abc
2	1	NULL
3	3	Mno
4	1	NULL
5	3	NULL

If you want to update one of the rows after this

UPDATE  @tbl1
SET     field2='test'
WHERE   id=2

returns

 1	1	Abc
2	1	test
3	3	Mno
4	1	NULL
5	3	NULL

Does that help at all?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-18 : 15:53:56

The output looks like below. If field2 is null and Field1 is 1 which is ID value, field2 will be update with Field2's value where ID=1

ID Field1 Field2
1 1 Abc
2 1 Abc
3 3 Mno
4 1 Abc
5 3 Mno

cblythe
Starting Member

6 Posts

Posted - 2010-05-18 : 15:59:03

Ok it isn't pretty but I'm not sure what the end use is. A series of statements like this would do the trick:

UPDATE  @tbl1
SET     field2='Abc'
WHERE   Field1=1
        AND Field2 IS NULL

UPDATE  @tbl1
SET     field2='Mno'
WHERE   Field1=3
        AND field2 IS NULL

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-18 : 16:01:07

No Cblythe,
i can not do one by one. i have millions records.

cblythe
Starting Member

6 Posts

Posted - 2010-05-18 : 16:04:01

quote:
Originally posted by rudba

No Cblythe,
i can not do one by one. i have millions records.

Ok how about this:


UPDATE a
SET a.Field2 = b.field2
FROM @tbl1 a INNER JOIN @tbl1 b ON a.field1 = b.Field1
WHERE a.Field2 IS NULL

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-18 : 16:21:19

works thanks a lot cblythe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources