[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3921
  • Last Modified:

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
0
smoothcat11
Asked:
smoothcat11
  • 2
1 Solution
 
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
 
David ToddSenior 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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now