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

Question

I have the following table, which I will call 'example'

id name last_name 01 Adam Adams 02 Bill Billo 03 Cathy McCathyson 

I need to change the table and eventually:

 id name 01 Adam Adams 02 Bill Billo 03 Cathy McCathyson 

For one line, I know how to write this query:

 UPDATE example SET name = (SELECT name FROM example WHERE id = 01)+" " +(SELECT last_name FROM example WHERE id = 01) WHERE id = 01; 

How can I change this query so that it updates each row with row values ​​such as in the example?

EDIT: I updated my example as it confused the problem.

Answer 1

 UPDATE example SET NAME = NAME + ' ' + last_name 

---

  ID NAME LAST_NAME 

---

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

Answer 2

You might want to add this as a computed column . Thus, the column is virtual, so you are not using the extra storage space. In addition, any changes to the name or last_name are automatically reflected in the new column without any intervention.

EDIT : modified code based on the change in the example.

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

Answer 3

NB: This answer was based on the first incarnation of the question. The same general principles apply to the revised version, so I did not update the column names to synchronize them.

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

I also suggested that non-columns are NULL. If they are then joined NULL , you will get NULL if you have ANSI default parameters. If this is not the desired behavior, you will 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 @example SELECT 01, '990000', 'Red' UNION ALL SELECT 02, '009900', 'Green' UNION ALL SELECT 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. This means that you will store a lot of redundant characters, which means that fewer lines will fit into the data page and more I / O and less efficient use of memory.