mysql update table column with multiple values from same table

Question

I have a mysql table with columns: customer, dateOrder. One customer can have orders in multiple dates. I want to add a new column with the farthest date order for each customer. So far i tried this:

UPDATE mytable  
   SET MINDATE = (SELECT min(DATEORDER) 
                    FROM (SELECT * 
                            FROM mytable 
                           GROUP 
                              BY CUSTOMER
                          ) tblTmp
                 )

, where tblTmp is a temporary table;The problem is that it brings the same date for all my customers (the farthest date in the table). Any ideas?

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. - A tip is to start with a SELECT that returns the desired result, and then build up to the UPDATE — Strawberry
– Strawberry, Commented Jan 26, 2015 at 15:00

Barmar · Accepted Answer · 2015-01-26 15:03:57Z

1

Use a JOIN to match the original table with the subquery:

UPDATE mytable AS t1
JOIN (SELECT customer, MIN(dateorder) AS mindate
      FROM mytable
      GROUP BY customer) AS t2 ON t1.customer = t2.customer
SET t1.mindate = t2.mindate

answered Jan 26, 2015 at 15:03

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bluesony Over a year ago

thanks! worked like a charm! I'm quite new with databases and I was avoiding the Join :)

Barmar Over a year ago

Embrace the JOIN! it is the fundamental operation in relational databases.

Collectives™ on Stack Overflow

mysql update table column with multiple values from same table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related