update 1 col with values of col2 and 3

Question

update 1 col with values of col2 and 3

Staggerlee

SSCertifiable

Points: 5272
More actions
January 1, 2012 at 11:46 am

#262248

Hi all,
simple one for you experts im sure. I have a table with values in col1 and col2
I want to put both values into a new col with text say col1 + " ," + col2
thanks
S

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

stebennettsjb (1/1/2012)
Hi all,
simple one for you experts im sure. I have a table with values in col1 and col2
I want to put both values into a new col with text say col1 + " ," + col2
thanks
S

CREATE TABLE UpdateCol

(Col1 INT NOT NULL,

COL2 INT NOT NULL,

COL3 INT NULL

)

INSERT INTO UpdateCol (Col1, COL2)

SELECT 1,2

UNION ALL

SELECT 2,3

GO

UPDATE tbl

SET Col3 = Col1 + Col2

FROM UpdateCol AS tbl

SELECT *

FROM UpdateCol;

Col1COL2COL3

123

235

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

UPDATE Mytable SET col3 = col1 + " ," + col2

If col1 or col2 are a numeric datatype, I'd explicitly cast.

If either col1 or col2 are nullable, then use ISNULL with an appropriate replacement.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Staggerlee SSCertifiable Points: 5272 More actions · Answer 3

Thanks Welsh Corgi!

Thanks for creating the test table..

Im trying to get col 3 to be

Col1 COL2 COL3

1 2 1, 2

2 3 2, 3

been playing with adding a variables and Col1 + " ," + Col2

But not going well 🙁

Hi Chris,

im getting

Msg 207, Level 16, State 1, Line 2

Invalid column name ' ,'.

Thanks for the replies!

S

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

CREATE TABLE UpdateColVar

(Col1 INT NOT NULL,

COL2 INT NOT NULL,

COL3 INT NULL)

GO

INSERT INTO UpdateColVar (Col1, COL2)

SELECT 1,2

UNION ALL

SELECT 2,3

GO

SELECT Col1, COL2, Col1 + COl2 AS Col3

FROM UpdateColVar

Please provide your DDL & INSERT Statements if this does not work for you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Staggerlee SSCertifiable Points: 5272 More actions · Answer 5

hi,

so first attempt:

UPDATE UpdateCol2

SET col3 = col1 + " ," + col2

I get

Msg 207, Level 16, State 1, Line 2

Invalid column name ' ,'.

--

Then i thought maybe use a variable. but wasnt sure how to do that :/

DECLARE @var varchar;

UPDATE tbl

SET @var = Col1 + ", " + Col2

SET Col3 = @MyCounter

FROM UpdateCol2 AS tbl

Which i get

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near '='.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I need your table structure and sample data.

Please look at the link in my signature block.

:w00t:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Staggerlee SSCertifiable Points: 5272 More actions · Answer 7

ohh sorry i was using your examples!

read the links when you replied.. Sorry for not adding them before :unsure:

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Sounds like you do not have a col3?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

I'm glad that you got a solution to your problem:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

stebennettsjb (1/1/2012)
Thanks Welsh Corgi!
Thanks for creating the test table..
Im trying to get col 3 to be
Col1 COL2 COL3
1 2 1, 2
2 3 2, 3
been playing with adding a variables and Col1 + " ," + Col2
But not going well 🙁
Hi Chris,
im getting
Msg 207, Level 16, State 1, Line 2
Invalid column name ' ,'.
Thanks for the replies!
S

Structure?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 11

stebennettsjb (1/1/2012)
Msg 207, Level 16, State 1, Line 2
Invalid column name ' ,'.

Use single quotes ste (Steve?) - double quotes indicate an object such as a column.

Like this:

SET col3 = CAST(col1 AS VARCHAR(5))+ ',' + CAST(col2 AS VARCHAR(5))

Edit - wrong post quoted

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

ChrisM@home (1/1/2012)
stebennettsjb (1/1/2012)
ohh sorry i was using your examples!
read the links when you replied.. Sorry for not adding them before :unsure:
Use single quotes ste (Steve?) - double quotes indicate an object such as a column.
Like this:
SET col3 = CAST(col1 AS VARCHAR(5))+ ',' + CAST(col2 AS VARCHAR(5))

How can you tell him what to do if you do not not know what the structure is?

If you do not know what the DDL is then you are talking garbage and you are contributing to the problem! 😛

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Staggerlee SSCertifiable Points: 5272 More actions · Answer 13

Hi Chris,

Thats got it working! thank you,

Welsh Corgi sorry for not explaining myself very well, I shall follow your links guide from now on.. Your free to breath a sigh of relief and move on from this thread.. i fear ive broken all the rules on this one.. apologies

Thanks to both of you for your help..

S

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

stebennettsjb (1/1/2012)
Hi Chris,
Thats got it working! thank you,
Welsh Corgi sorry for not explaining myself very well, I shall follow your links guide from now on.. Your free to breath a sigh of relief and move on from this thread.. i fear ive broken all the rules on this one.. apologies
Thanks to both of you for your help..
S

Sorry, I misread you post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/