Home » SQL & PL/SQL » SQL & PL/SQL » updating a table
updating a table [message #200026] Fri, 27 October 2006 08:20 Go to next message
cleopard
Messages: 10
Registered: June 2005
Junior Member
I have some data that I want to move from one table (call it table1) to another (table2). I've added a couple of new fields to table2 and will populate them with data from table1. I'm trying to use an SQL statement with the basic form of:

update table2
set table2.field1 =
(
select statement to get data from table1 that I want moved
)


but since the select statement fetches a number of rows and the 'set' is only going to set one at a time, that won't work as is. It seems that when I try to find examples of 'update' statements, the 'set' part seems to have data for only one record, but not multiple records like I'm trying to do. Am I on the right track? Thanks for any help.
Re: updating a table [message #200029 is a reply to message #200026] Fri, 27 October 2006 08:35 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Have you heard of correlated queries?

You can filter the inner query with the outer query's columns to get single row.

By
Vamsi
Re: updating a table [message #200031 is a reply to message #200026] Fri, 27 October 2006 09:01 Go to previous messageGo to next message
cleopard
Messages: 10
Registered: June 2005
Junior Member
So, referring to the query example in my original note, instead of simply having "set table2.field1 = ...", have a query in its place that denotes the fields that need to be set?
Re: updating a table [message #200033 is a reply to message #200031] Fri, 27 October 2006 09:09 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Correlated Update
Single column

UPDATE TABLE(<SELECT STATEMENT>) <alias>
SET <column_name> = (
SELECT <column_name>
FROM <table_name> <alias>
WHERE <alias.table_name> = <alias.table_name>;


UPDATE employees t1
SET salary = (
SELECT salary
FROM empbak t2
WHERE t1.employee_id = t2.employee_id);

Multi-column

UPDATE TABLE(<SELECT STATEMENT>) <alias>
SET <column_name> = <value>;

UPDATE table1 t_alias1
SET (col1, col2) = (
SELECT col1, col2
FROM table2 t_alias2
WHERE t_alias1.col3 = t_alias2.col3);
icon7.gif  Re: updating a table [message #200076 is a reply to message #200026] Fri, 27 October 2006 15:03 Go to previous message
cleopard
Messages: 10
Registered: June 2005
Junior Member
Many thanks for the input, tahpush and Vamsi. As it turns out, I was very close to what tahpush wrote, but not quite.
Previous Topic: ORA-01502: index 'CCMSLIVE.SYS_C006632' or partition of such index is in unusable state
Next Topic: Select 3 different 'WHERE's from same table.
Goto Forum:
  


Current Time: Fri Dec 02 22:37:50 CST 2016

Total time taken to generate the page: 0.10311 seconds