Re: Find the closest previous date and update matching records from another table

[Peter Brawley]

You're hoping someone will debug your DDL and inserts then write your update query for you? You're more likely to get help here if you post your attempts at the job and describe where you're stuck.

First, issues in your requirement statement ...

- "find the closest previous sdt from returndate" needs clarification: does it mean "for each row, find the samplereturns row with the smallest negative value of (sdt - returndate), and if such is found, what is the requirement when multiple rows match the criterion?

- re "id_reference is a unique number combinateion of idno and returndate"., DDL for the 2nd table doesn't define such a primary or unique key.

And in your code, obvious errors ...

- missing terminating semicolons,

- missing quote in the first set of inserts,

- illegal character '@' in a table name

Finally, when you post your try at the query, if we're to help, we'll need your MySQL version.