Concatenate SQL Records with UPDATE

Hello, I'm using SQL Server 2005 and I want to update the name in over 18000 records

I want it to be:  name (size) instead of name:

example:
Vitamin A (60 Caps)
right now its:
Vitamin A

Update b
Set b.Name = b.Name' ('a.Size)'
From Product b, newproducturlinfo a
WHERE b.SKU LIKE a.UPC AND b.ProductID = 32167

I keep getting this:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ' ('.

I'm using ProductID = 32167 because I don't want to affect more then 1 record if it goes wrong.  Any info will help.  Thanks
smoothcat11Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Can you post the table structure(s) and some sample data.

Any reason why these fields are ntext instead of say nvarchar( max )?

Cheers
  David
0
 
David ToddSenior DBACommented:
Hi,

Your concatenation was wrong.

And changed the join syntax

HTH
  David
update b
set b.Name = b.Name + ' (' + a.Size + ')'
from dbo.Product b
inner join dbo.newproducturlinfo a
	on b.SKU like a.UPC
where
	b.ProductID = 32167

Open in new window

0
 
smoothcat11Author Commented:
Msg 402, Level 16, State 1, Line 1
The data types nvarchar and ntext are incompatible in the add operator.

Just got this.

They are ntext
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
update b
set b.Name = b.Name + ' (' + cast(a.Size as nvarchar)  + ')'
from dbo.Product b
inner join dbo.newproducturlinfo a
        on b.SKU like a.UPC
where
        b.ProductID = 32167
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.