Mysql: Update table with select max of another table [duplicate]

Question

I have 2 different database in 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 DB1.contacts.contacts table where DB2.paperworks.name = DB1.contacts.name

My desidered output should be:

Second table after query DB2.paperworks:

id | name | contact_id
1  | foo  | 210

This is my query:

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 che "MAX(code)" condition. Can you help me, please?

not necessarily duplicate, since this could also be done with a simple update using correlated subquery, as shown in an answer below. — SlimsGhost
– SlimsGhost, Commented Jan 20, 2017 at 15:05
@SlimsGhost, well most questions have multiple possible answers. That doesn't mean they cannot have duplicates. I'm merely trying to direct OP to an answer of a question that seems relevant to his own. — Chris
– Chris, Commented Jan 20, 2017 at 15:07
@Chris, fair enough, just didn't want it to get closed with only a pointer to what might not be a desirable answer. — SlimsGhost
– SlimsGhost, Commented Jan 20, 2017 at 15:10

SlimsGhost · Accepted Answer · 2017-01-20 15:04:24Z

6

A slightly simpler form of update will 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
);

answered Jan 20, 2017 at 15:04

SlimsGhost

2,9091 gold badge12 silver badges16 bronze badges

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

Comments

Bohemian · Accepted Answer · 2017-01-20 15:05:55Z

0

Try this:

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

answered Jan 20, 2017 at 15:05

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

Mysql: Update table with select max of another table [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related