Mysql: Refresh a table using select max of another table

Question

I have 2 different databases on my MySql server.

First table DB1.contacts:

id | name | code
1  | foo  | 157
2  | foo  | 95
3  | foo  | 210

Second table DB2.paperworks:

id | name | contact_id
1  | foo  | 0

I would like to update DB2.paperworks, set DB2.paperworks.contact_id = max (DB1.contacts.code) of the DB1.contacts.contacts table, where DB2.paperworks.name = DB1.contacts.name

My expected output should be:

Second table after DB2.paperworks query:

id | name | contact_id
1  | foo  | 210

This is my request:

UPDATE DB2.paperworks
JOIN DB1.contacts
ON DB2.paperworks.name = DB1.contacts.name
SET DB2.paperworks.contact_id = DB1.contacts.code

I don’t understand how to write the condition "MAX (code)". Can you help me please?

Answer 1

A slightly simpler form updatewill do the trick:

UPDATE DB2.paperworks
SET DB2.paperworks.contact_id = (
    select max(DB1.contacts.code)
    from DB1.contacts
    where DB1.contacts.name = DB2.paperworks.name
    group by DB1.contacts.code
);

Answer 2

Try the following:

UPDATE DB2.paperworks
SET DB2.paperworks.contact_id = (
    SELECT MAX(DB1.contacts.code)
    FROM DB1.contacts
    WHERE DB2.paperworks.name = DB1.contacts.name
)