?
Solved

Problem Updating a column

Posted on 2007-11-19
7
Medium Priority
?
220 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

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.
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 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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

762 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