September 8, 2011 at 1:13 am
Hi,
This is really simple.
This is what I have:
UPDATE Community
Set NetworkLogin = 'c' & ID
We want the column NetworkLogin to show a letter 'c' and then the ID which is in another column, for example, ID 1234 we want it to show c1234.
I get 'Conversion failed when converting the varchar value 'c' to data type int.'
Not sure what I'm doing.
Thanks
Ben
September 8, 2011 at 1:38 am
There are a couple of thing wrong with your update statement...
First, SQL server uses + to concatenate values, not &
Second, I assume ID is an integer data type (e.g. integer), so SQL Server is trying convert 'c' to an integer, which obviously can't be done.
Try this instead...
UPDATE Community
Set NetworkLogin = 'c' + cast(id as varchar(255))
September 8, 2011 at 4:26 pm
Awesome, that's great.
Can I push my luck even further...
We'd like it to update only records where a column in another table equals a particular value. For example, another table called Constituencies with a column called ConstitCode where the value equals @PC.:-) We would link on ID.
Ben
September 8, 2011 at 4:33 pm
Or will this work,
UPDATE Community
Set NetworkLogin = 'c' + cast(id as varchar(255))
FROM Community INNER JOIN
Constituencies ON "ID" = "ID"
WHERE ConstitCode = '@PC'
September 9, 2011 at 1:23 am
It will work u just need to change something
UPDATE Community
Set NetworkLogin = 'c' + cast(id as varchar(255))
FROM Community c INNER JOIN
Constituencies cn ON c.ID = cn.ID
WHERE cn.ConstitCode = @PC
in join condition u don't need to mention the id without double quotes.
September 9, 2011 at 2:11 am
ghanshyam.kundu (9/9/2011)
It will work u just need to change something
UPDATE Community
Set NetworkLogin = 'c' + cast(id as varchar(255))
FROM Community c INNER JOIN
Constituencies cn ON c.ID = cn.ID
WHERE cn.ConstitCode = @PC
in join condition u don't need to mention the id without double quotes.
If the OP is looking for the literal value @PC, he will need to put the single quotes around it.
I don't get the impression from the OP that @PC is a parameter.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply