Home » SQL & PL/SQL » SQL & PL/SQL » Updating table with existing table data
Updating table with existing table data [message #252438] Wed, 18 July 2007 17:20 Go to next message
clintonf
Messages: 82
Registered: May 2006
Member
Hi all,

What SQL statement would I use to update a table row with data from another row in the same table?

eg. In my employees table I have name, address & phone number for Jack. Then Mary, an existing employee, moves in with Jack. I'd like to use an update statement to grab address and phone number from Jack and apply it to Mary.

I could just do the statements field-by-field but the real-life case that I am considering consists of upwards of 30 fields.

I know there has to be an easy answer to this but I can't think of what it is or even formulate a decent google search.

Thanks!
Re: Updating table with existing table data [message #252439 is a reply to message #252438] Wed, 18 July 2007 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
How do you determine/decide which source row goes to which target row?
Re: Updating table with existing table data [message #252440 is a reply to message #252438] Wed, 18 July 2007 17:31 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
I will supply that info manually... if I am getting your question correctly.

Something like:

UPDATE employees SET address, phone_number =
     (SELECT address, phone_number FROM employees
     WHERE name = 'Jack')
WHERE name = 'Mary';
Re: Updating table with existing table data [message #252441 is a reply to message #252438] Wed, 18 July 2007 17:37 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
Your sample code look OK to me.
Re: Updating table with existing table data [message #252442 is a reply to message #252438] Wed, 18 July 2007 17:42 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
I've tried that, in a stored procedure, but when I try to compile it I get the following error:

PL/SQL: ORA-00927: missing equal sign
PL/SQL: SQL Statement ignored


I tried the statement with just one field and that works OK, but two or more gives the above error.
Re: Updating table with existing table data [message #252443 is a reply to message #252438] Wed, 18 July 2007 17:47 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
If you'd read & follow the STICKY post at top of forum which suggests posters include DDL to create & populate tables, it
would make it easier for folks to assist.
IIRC, it can be done but I don't recall 100% correct syntax.
(SELECT (address, phone_number) FROM employees WHERE name = 'Jack')

above might work better, then again it might not.
Re: Updating table with existing table data [message #252444 is a reply to message #252438] Wed, 18 July 2007 17:49 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
I think I found it - try:
UPDATE employees SET (address, phone_number) =
Re: Updating table with existing table data [message #252446 is a reply to message #252438] Wed, 18 July 2007 17:51 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Aha, excellent!

Thanks very much.

I knew it would be something simple Embarassed
Re: Updating table with existing table data [message #252447 is a reply to message #252438] Wed, 18 July 2007 17:54 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
Please post the complete working SQL so those that follow will have a clear & concise answer.
Re: Updating table with existing table data [message #252448 is a reply to message #252438] Wed, 18 July 2007 17:58 Go to previous message
clintonf
Messages: 82
Registered: May 2006
Member
Ah yes, good idea.

The solution would be:

UPDATE employees SET (address, phone_number) =
     (SELECT address, phone_number FROM employees
     WHERE name = 'Jack')
WHERE name = 'Mary';


Note the parentheses in the first line.
Previous Topic: sorting and query tuning
Next Topic: One time only procedure
Goto Forum:
  


Current Time: Mon Dec 05 14:42:05 CST 2016

Total time taken to generate the page: 0.21906 seconds