Home » SQL & PL/SQL » SQL & PL/SQL » Update values
Update values [message #299118] Sat, 09 February 2008 10:43 Go to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
Hi there. I have a question.
On my order table. I have got

Order_ID Customer_ID    Order_date  Ship_date
1         100           01-SEPT-99  02-SEPT-99
2         101           02-SEPT-99  05-SEPT-99
3         103           30-AUG-99   04-SEPT-99
4         103           22-AUG-99   04-SEPT-99
5         103           21-AUG-99   04-SEPT-99
6         104           22-AUG-99   03-SEPT-99


How do I update the table where the customers who ordered more than once and have the same ship date, we put the earliest date of order_date. For example, Customer 103 made more than 1 order and has the same ship_date therefore we going to update that table and replace the order_date with the earliest order date of that order.
The result will be something like this:

Order_ID Customer_ID    Order_date  Ship_date
1         100           01-SEPT-99  02-SEPT-99
2         101           02-SEPT-99  05-SEPT-99
3         103           21-AUG-99   04-SEPT-99
4         103           21-AUG-99   04-SEPT-99
5         103           21-AUG-99   04-SEPT-99
6         104           22-AUG-99   03-SEPT-99
Re: Update values [message #299119 is a reply to message #299118] Sat, 09 February 2008 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First find the select statement that gives you the first date within your conditions, then update all the rows of the same group with this date.

Regards
Michel
Re: Update values [message #299120 is a reply to message #299118] Sat, 09 February 2008 11:45 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Maybe something like this ...

UPDATE orders o
SET o.orderdate =
  (SELECT MIN(o2.orderdate) orderdate
   FROM orders o2
   WHERE o2.customerid = o.customerid
   AND o2.shipdate = o.shipdate)
WHERE(o.customerid,   o.shipdate) IN
  (SELECT customerid,
     shipdate
   FROM orders
   GROUP BY customerid,
     shipdate HAVING COUNT(1) > 1)


Cheers,
Michael.
Re: Update values [message #299122 is a reply to message #299120] Sat, 09 February 2008 12:01 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't give solution in Newbie forum, give hint/clue to help them find the solution and so learn.

Regards
Michel
Previous Topic: Insert into table
Next Topic: Materalized view
Goto Forum:
  


Current Time: Wed Dec 07 03:14:50 CST 2016

Total time taken to generate the page: 0.14069 seconds