?
Solved

Problem Updating a column

Posted on 2007-11-19
7
Medium Priority
?
223 Views
Last Modified: 2013-12-07
Hi,

I have three tables.

Table One

table1.ID   col1, col2, col3, col4, col5, table2.dateto, table3.datefrom

Table two
id, dateto

Table three
id, datefrom

I want to merge these into one table.

If i do a select into new table i get a cartesian product:

select
table1ID,   col1, col2, col3, col4, col5, dateto, datefrom
into NewTable
from
Table1,
Table2,
Table3
where
Table1.Id=Table2.id
and
Table1.id=Table3.id


If i do an update statement i get a a subquery must return one value using an = IE:

update table1 set datefrom= (select dateto from table2.)
0
Comment
Question by:wilflife
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:jwahl
ID: 20311790
your description is not clear:
Is Table1 the merged table?
Do you have more than one record in Table2/Table3 for one record in Table1?

Your update fails because the join condition is missing:



update table1 set datefrom= (select dateto from table2 where table2.id = table1.id);

Open in new window

0
 

Author Comment

by:wilflife
ID: 20311940
Hi,

The join condition does not work as it stil returns more than one item:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Table1 is the table i want the data into. I use the select into clause to get the data into one table not to overwrite my original table, once the data is correct i'll move it into table one.

I hope this is clearer.
0
 
LVL 12

Expert Comment

by:jwahl
ID: 20312067
there are more than one row in table2 for one record in table1, so an update is not possible. you can use MAX() or MIN() as workaround:


update table1 set datefrom= (select MAX(dateto) from table2 where table2.id = table1.id);

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:wilflife
ID: 20312139
Hi,
I solved it by doing a group by. It was duplicating my records six times not sure why, group by all the columns solved it.
0
 
LVL 12

Expert Comment

by:jwahl
ID: 20312173
so you had duplicates in table2 and/or table3.
with DISTINCT you'll get the same result:


select DISTINCT
table1ID,   col1, col2, col3, col4, col5, dateto, datefrom
into NewTable
from
Table1,
Table2,
Table3
...

Open in new window

0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 20953215
PAQed with points refunded (500)

Computer101
Community Support Moderator
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question