Solved

How to load data into a table based on select criteria from another table

Posted on 2008-10-03
2
201 Views
Last Modified: 2010-04-21
I have a table Order . I need to load data into another table OrderDuplicates based on a select criteria where there are more than one occurrence of the following two column grouping, OrderID, OrderNumber.
Order and OrderDuplicates have same structure

Order
---------
OrderID
OrderNumber
OrderDate
OrderPrice
OrderUserid

So if I have the foll data
OrderID     OrderNumber    OrderDate            OrderPrice      OrderUserid
1                21                      09/01/2008             10.00           abc
1                21                      09/02/2008             12.00            lmn
1                21                       09/03/2008             11.00           xyz
1               20                       09/01/2008                10.00m       abc
2               19                       09/01/2008               9.00            xyz
2               19                       09/02/2008               10.00            abc
3               13                      09/02/2008               10.00             xyz
5               12                      09/03/2008               11.00            abc
6               12                       09/01/2008              10.00             xyz
6               12                       09/03/2008               9.00                abx

I would want the foll rows loaded into OrderDuplicates
1                21                      09/01/2008             10.00           abc
1                21                      09/02/2008             12.00            lmn
1                21                       09/03/2008             11.00           xyz
2               19                       09/01/2008               9.00            xyz
2               19                       09/02/2008               10.00            abc
6               12                       09/01/2008              10.00             xyz
6               12                       09/03/2008               9.00                abx



0
Comment
Question by:countrymeister
[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
2 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22633692
insert into orderduplicates(fieldlist)
select o.*
from
order o
join
(
select orderid, ordernumber
from order
group by orderid, ordernumber
having count(*) > 1
) d on o.orderid = d.orderid and o.ordernumber = d.ordernumber
0
 
LVL 1

Author Closing Comment

by:countrymeister
ID: 31502745
Great, thanks for your help and for the quick, accurate answer.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

627 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