How to update multiple column value into one colu

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)

How to update multiple column value into one colu

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-01-25 : 19:17:20

Hi Everyone,

How do i update Field3 with Filed1;Field2 values.
Please take a look output value:

declare @tbl1 table
(ID INT,
Field1 varchar(50),
Field2 varchar(50),
Field3 varchar (255)
)

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

INSERT INTO @tbl1
SELECT 2,'Bcc','Bcc01',null

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

INSERT INTO @tbl1
SELECT 4,'Xyz','Xyz01',null

INSERT INTO @tbl1
SELECT 5,'XXX', 'XXX01',null

Output
Id Field1 Field2 Field3
1 Abc Abc01 Abc;Abc01
2 Bcc Bcc01 Bcc;Bcc01

ZZartin
Starting Member

30 Posts

Posted - 2011-01-25 : 19:30:44

Should be pretty straight forward.

UPDATE table SET Field3 = Field1 + ';' + Field2

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-01-25 : 19:43:50

the problem is if i have null vlaue on Field1 but not null in field2, it will be update null in field3 too

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-01-25 : 19:58:36

Lets try add one more field and update Field4

declare @tbl1 table
(ID INT,
Field1 varchar(50),
Field2 varchar(50),
Field3 varchar(50),
Field4 varchar (255)
)

INSERT INTO @tbl1
SELECT 1,'Abc','Abc01','Abc02',null

INSERT INTO @tbl1
SELECT 2,'Bcc','Bcc01',null,null

INSERT INTO @tbl1
SELECT 3,null,'Mno01','Mno02',null

INSERT INTO @tbl1
SELECT 4,'Xyz',null,'Xyz02',null

INSERT INTO @tbl1
SELECT 5,'XXX', 'XXX01','Xxx02',null

UPDATE @tbl1 SET Field4 = Field1 + ';' + Field2 + ';' + Field3

select * from @tbl1

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-26 : 07:47:21

Use IsNull in the updates e.g.

declare @tbl1 table
(ID INT,
Field1 varchar(50),
Field2 varchar(50),
Field3 varchar(50),
Field4 varchar (255)
)

INSERT INTO @tbl1
SELECT 1,'Abc','Abc01','Abc02',null

INSERT INTO @tbl1
SELECT 2,'Bcc','Bcc01',null,null

INSERT INTO @tbl1
SELECT 3,null,'Mno01','Mno02',null

INSERT INTO @tbl1
SELECT 4,'Xyz',null,'Xyz02',null

INSERT INTO @tbl1
SELECT 5,'XXX', 'XXX01','Xxx02',null

UPDATE @tbl1 SET Field4 = isnull(Field1,'') + ';' + isnull(Field2,'') + ';' + isnull(Field3,'')

select * from @tbl1

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-01-26 : 10:42:47

Thank you Mik,
Still there is double ; if there is null. Please take a look output.

ID Field1 Field2 Field3 Field4
1 Abc Abc01 Abc02 Abc;Abc01;Abc02
2 Bcc Bcc01 NULL Bcc;Bcc01;
3 NULL Mno01 Mno02 ;Mno01;Mno02
4 Xyz NULL Xyz02 Xyz;;Xyz02
5 XXX XXX01 Xxx02 XXX;XXX01;Xxx02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources