Quick SQL question...

I'm trying to change every value of a certain column in a table by adding an
extra character to it:
UPDATE event_details
SET code_event = (SELECT code_event + '0' FROM event_details)
But I know I need some sort of join to do this but I'm not sure what. Can
any one advise please?
Cheers,
elziko> CREATE TABLE #Temp
I need to do this without using CREATE TABLE
Any ideas?
Cheers,
elziko|||elziko
I was creating table for repro your question because you did not post DDL.
Just use UPDATE Tablename.............
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:#BHQ$tt#DHA.2184@.TK2MSFTNGP12.phx.gbl...
> I need to do this without using CREATE TABLE
> Any ideas?
> --
> Cheers,
> elziko
>|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:eQLPHRt%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I'm trying to change every value of a certain column in a table by adding
an
> extra character to it:
> UPDATE event_details
> SET code_event = (SELECT code_event + '0' FROM event_details)
> But I know I need some sort of join to do this but I'm not sure what. Can
> any one advise please?
> --
> Cheers,
> elziko
>|||Thanks, but how would I only update the rows who are already only seven
characters long? Where would I put the where clause?
Cheers,
elziko|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details where LEN(code_event)
= 7
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter where LEN(code_event) =
7
I am not sure exactly what you are trying to achieve. The above code will
add a '0' to every code_event that is 7 characters long. If your 7
character/digit code_events are different then I would suggest using a
cursor.
Cheers,
Andre
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:403b730c$0$21923$afc38c87@.news.easynet.co.uk...
> Thanks, but how would I only update the rows who are already only seven
> characters long? Where would I put the where clause?
> --
> Cheers,
> elziko
>|||> I am not sure exactly what you are trying to achieve. The above code will
> add a '0' to every code_event that is 7 characters long. If your 7
> character/digit code_events are different then I would suggest using a
> cursor.
Yeah thats exactly what I want to do and thats where I initially put the
WHERE clauses before replying to you! But I get the following error:
MED0017 0
Server: Msg 8152, Level 16, State 9, Line 13
String or binary data would be truncated.
The statement has been terminated.
When I do the print of the NewCharacter it seems to have a space in it but
the column I'm updating is only 8 chars long so it looks like this is the
problem? Where is that space coming from? Or is something else wrong here?
Thanks a lot for your help.
Cheers,
elziko