Solved

Problem Updating a column

Posted on 2007-11-19
7
215 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 54
Help With Database JOIN 7 27
SQL Server Count where two id types exist in column 8 27
SQL Server - Set Value of Multiple Fields in One Query 10 22
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now