Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

I have the following table which I'll call 'example'

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

I need to modify the table and end up with the following:

id name
01 Adam Adams
02 Bill Billo
03 Cathy McCathyson

For a single row, 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 do I modify this query such that it updates each row with that row's values, as in the example?

EDIT: I've updated my example since it confused the issue.

like image 220
David Smith Avatar asked Dec 01 '10 20:12

David Smith


People also ask

How do I update multiple rows in SQL with update?

just make a transaction statement, with multiple update statement and commit. In error case, you can just rollback modification handle by starting transaction.

Which SQL commands do you use to update the values of an existing field?

The UPDATE statement in SQL is used to update the data of an existing table in database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement. UPDATE table_name SET column1 = value1, column2 = value2,...


1 Answers

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
like image 75
Sathyajith Bhat Avatar answered Sep 28 '22 03:09

Sathyajith Bhat