Update with subquery in MySql

Question

I've two tables:

work (work_id (AI, PK), sent_date, received_date, visit_date)
history_work(id_history_work (AI, PK), work_id (FK), sent_date, reseived_date, visit_date)

Relationship shoud be 1->n.

I want to update work table so sent_date, received_date and visit_date shoud have the values of last inserted record in history_work table (last id_history value) with same work_id value.

Gordon Linoff · Accepted Answer · 2014-09-14 22:43:29Z

1

You can do this by using join. Join once to the history table. Join a second time to get the maximum id (which is presumably the most recent insertion).

update work w join
       history h
       on w.work_id = h.work_id join
       (select work_id, max(id_history_work) as maxihw
        from history
        group by work_id
       ) hw
       on hw.maxihw = h.id_work_history
    set w.sent_date = h.sent_date,
        w.received_date = h.received_date,
        w.visit_date = h.visit_date;

answered Sep 14, 2014 at 22:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Update with subquery in MySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related