|
Home > Archive > MS SQL Server > December 2006 > Appending from one column to another for every row in a table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Appending from one column to another for every row in a table
|
|
| Simon Harvey 2006-12-13, 7:12 pm |
| Hi there,
I need to carry out a database cleaning/maintenance operation whereby I
take the text in one column called ShortDescription and add it to the
text in a column called AdditionalDetails.
I know how to copy from one to the other but that would have the effect
of overwriting the values in the AdditionalDetails column. I need
whatever is in the first column to be simply appended to whatever is in
the second. If the first has null in it, nothing should happen, and I
guess if the second has a null in it, the null should be replaced with
whatever's in the second column.
Can anyone advise me on how to do this? I'm sure it must be a relatively
simple operation but I've never seen it done.
Thanks to anyone who can help
Simon
| |
|
|
Update table1 set additionaldetails = isnull(additionaldet
ails,'') +
shortdescription
where shortdescription is not null
Jay
Simon Harvey wrote:
> Hi there,
>
> I need to carry out a database cleaning/maintenance operation whereby I
> take the text in one column called ShortDescription and add it to the
> text in a column called AdditionalDetails
>
> I know how to copy from one to the other but that would have the effect
> of overwriting the values in the AdditionalDetails column. I need
> whatever is in the first column to be simply appended to whatever is in
> the second. If the first has null in it, nothing should happen, and I
> guess if the second has a null in it, the null should be replaced with
> whatever's in the second column.
>
> Can anyone advise me on how to do this? I'm sure it must be a relatively
> simple operation but I've never seen it done.
>
> Thanks to anyone who can help
>
> Simon
| |
| Roy Harvey 2006-12-13, 7:12 pm |
| You need concatenation, which is implemented using the + character.
UPDATE
SET AdditionalDetails = AdditionalDetails + ShortDescription
WHERE ShortDescription IS NOT NULL
The problem with the query above is that if AdditionalDetails is NULL
the concatenation ends up as NULL. So the more robust version would
be:
UPDATE
SET AdditionalDetails = COALESCE(AdditionalD
etails, '') +
ShortDescription
WHERE ShortDescription IS NOT NULL
Roy Harvey
Beacon Falls, CT
On Wed, 13 Dec 2006 16:20:40 +0000, Simon Harvey
<nothanks@hotmail.com> wrote:
>Hi there,
>
>I need to carry out a database cleaning/maintenance operation whereby I
>take the text in one column called ShortDescription and add it to the
>text in a column called AdditionalDetails.
>
>I know how to copy from one to the other but that would have the effect
>of overwriting the values in the AdditionalDetails column. I need
>whatever is in the first column to be simply appended to whatever is in
>the second. If the first has null in it, nothing should happen, and I
>guess if the second has a null in it, the null should be replaced with
>whatever's in the second column.
>
>Can anyone advise me on how to do this? I'm sure it must be a relatively
>simple operation but I've never seen it done.
>
>Thanks to anyone who can help
>
>Simon
| |
| Simon Harvey 2006-12-14, 5:25 am |
| You are both SQL Stalions!
I knew it would be simple!
Thank you - you've been a great help
Kindest Regards
Simon
|
|
|
|
|