Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem Updating a column

Posted on 2007-11-19
7
Medium Priority
?
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

609 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