Solved

Problem Updating a column

Posted on 2007-11-19
7
216 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
performance query 4 32
Oracle Query - Convert letters to numbers and display the difference 3 30
SQL2016 to ORACLE11G linked-server 6 28
Comparison query - 4 columns 9 25
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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

827 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