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

[Joni villa]

Hi,

I'm looking to find the closest previous sdt from returndate and get the associated id_reference by idno and update the id_reference in @sampleship table. id_reference is a unique number combinateion of idno and returndate.

I have a query that I made in TSQL, any help to convert this to mySQL to fit in my requirements or is there any other alternative approach.

I'm using MySQL Workbench 8.0 CE

Below is a sample DDL and desired result. Thank you in advance.

create table sampleReturns(idno varchar(35),id varchar(35), returndate
datetime, id_reference varchar(60))
insert into sampleReturns (idno,id, returndate, id_reference )
values ('12345670','123456PH','2022-04-26 00:00:00','2022-04-26 00:00:0012345670'),
('12345678','123456AB','022-01-31 00:00:00',2022-01-31 00:00:0012345678'),
('12345678','123456AB','2022-02-18 00:00:00','2022-02-18 00:00:0012345678'),
('12345678','123456AB','2022-05-25 00:00:00','2022-05-25 00:00:0012345678'),
('12345650','123456TW','2022-03-01 00:00:00','2022-03-01 00:00:0012345650'),
('22345688','223456PK','2022-01-21 00:00:00','2022-01-21 00:00:0022345688'),
('22345688','223456PK','2022-03-08 00:00:00','2022-03-08 00:00:0022345688')


create table @sampleship table(idno varchar(35),id varchar(35), sdt datetime, id_reference varchar(60))
insert into @sampleship table (idno,into, sdt, id_reference )
values ('12345670','123456PH','2020-11-26 00:00:00',NULL),
('12345670','123456PH','2022-04-23 00:00:00',NULL),
('12345670','123456PH','2022-07-25 00:00:00',NULL),
('12345678','123456AB','2022-01-31 00:00:00',NULL),
('12345678','123456AB','2022-08-25 00:00:00',NULL),
('12345678','123456AB','2022-02-22 00:00:00',NULL),
('12345650','123456TW','2022-02-25 00:00:00',NULL),
('22345688','223456PK','2022-01-31 00:00:00',NULL),
('22345688','223456PK','2022-05-25 00:00:00',NULL)

Desired result
@sampleship table
+---------+---------+-------------------+---------------------------+
|idno | id | sdt | id_reference |
+---------+---------+-------------------+---------------------------+
|12345670 |123456PH |2020-11-26 00:00:00|NULL |
|12345670 |123456PH |2022-04-23 00:00:00|2022-04-26 00:00:0012345670|
|12345670 |123456PH |2022-07-25 00:00:00|NULL |
|12345678 |123456AB |2022-01-31 00:00:00|2022-02-18 00:00:0012345678|
|12345678 |123456AB |2022-08-25 00:00:00|2022-05-25 00:00:0012345678|
|12345678 |123456AB |2022-02-22 00:00:00|NULL |
|12345650 |123456TW |2022-02-25 00:00:00|2022-03-01 00:00:0012345650|
|22345688 |223456PK |2022-01-31 00:00:00|2022-03-08 00:00:0022345688|
|22345688 |223456PK |2022-05-25 00:00:00|NULL |
+---------+---------+-------------------+---------------------------+