Using SQL, how do I update rows, using their own values?

UPDATE example SET NAME = NAME + ' ' + last_name

    ID NAME      LAST_NAME

     1 Adam       Adams     2 Bill       Billo     3 Cathy      McCathysonSQL> UPDATE example SET NAME = NAME + ' ' + last_name  2  /3 rows updatedSQL> select * from example  2  /        ID NAME                    LAST_NAME---------- -----------------------------------------         1 Adam Adams              Adams         2 Bill Billo              Billo         3 Cathy McCathyson        McCathyson

You might want to consider adding this as a computed column. That way the column is virtual, so you're not using extra storage space. Also, any changes to name or last_name are automatically reflected in the new column without any intervention.

EDIT: Modified code based on change in example.

alter table example    add full_name as coalesce(name+' ','') + last_name

NB: This answer was based on the first incarnation of the question. The same general principles apply to the revised version so I haven't updated the column names to keep in synch.

@n8wrl raises a good question in the comments but to answer your question (assuming that color and text are both character data and so do not need any casting).

Also I have assumed that non of the columns are nullable. If they are then concatenating NULL will yield NULL assuming you have ANSI default options. If that is not the desired behaviour you would need to use ISNULL(color,'') etc.

DECLARE @example  table (id int NOT NULL,color char(6) NOT NULL,text varchar(100) NOT NULL)insert into @exampleSELECT 01, '990000',   'Red' UNION ALLSELECT 02, '009900',   'Green' UNION ALLSELECT 03, '000099',   'Blue'UPDATE @example SET text = '<span style=''color: #'   +color+'''>'   +text+'</span>'/*WHERE id = 01;*/SELECT * FROM @example

I have to say that I doubt that storing it in this format is a good idea. It means that you will be storing a lot of redundant characters which means less rows will fit on a data page and greater I/O and less efficient use of memory