Updating table A with information from table B (containing duplicate information)

Question

This is a bit complicated. I have someone with a very specific request.

This database has two tables: Table A (customer table) and Table B (order table).

Table A has a unique customer identifier (customerid) for each row. It also has a Date of Service (DATEOFS) column, which is mostly populated with old information from a previous database.

Table B has a unique serial number (ordernum) for each order. Each order is also associated with a customer (customerid) and has an order date (dofserv).

To show the service date in a real PHP script, I use the following query:

SELECT dofserv FROM orders WHERE customerid=" . $result['customerid'] . " ORDER BY dofserv DESC LIMIT 1

This returns me the latest service date for one customer.

What I'm trying to do is update DATEOFS in table A using the most recent DOFSERV from table B.

As I understand it, the following query will work if there is only one matching line:

UPDATE clients a, orders b SET a.DATEOFS = b.DOFSERV WHERE a.customerid = b.customerid

However, since the order table has several rows that match the customer ID, I am not sure how to update clients.DATEOFS with only the most recent .DOFSERV orders.

Any suggestions? I would like to do this in pure MySQL to run from the command line; I could do this in PHP, but I'm not sure how long it will take to run.

Answer 1

One way to do this:

UPDATE clients a
SET
    a.DATEOFS=(SELECT MAX(b.DATEOFS) FROM orders b WHERE a.customerid=b.customerid);

NULL a.DATEOFS, . , , :

UPDATE clients a
    JOIN orders o ON a.customer_id=o.customer_id
SET
    a.DATEOFS=(SELECT MAX(b.DATEOFS) FROM orders b WHERE a.customerid=b.customerid);

clients, orders .

Answer 2

join clients :

UPDATE clients a
JOIN   (SELECT   customer_id, MAX(dofserv) AS md
        FROM     orders
        GROUP BY customer_id) b ON a.customerid = b.customerid
SET    a.dateofs = b.md

Answer 3

This is one way that will only update the minimum number of rows:

update clients a
set dofserv = ( select max(dofserv)
                from orders b
                where b.customerid = a.customerid
              )
where exists  ( select *
                from orders b
                where b.customerid = a.customerid
                  and  b.dofserv   > a.dofserve
              )