--liquibase formatted sql
--changeset patrick:update-orders-set-dealer-return-numbers-152363749-mariadb
update orders o,
(
    select
    order_id, json_unquote(json_extract(`blob`,'$.request.rlms_number')) AS `rlms_number`
    from ordersrr
    where `type` = 2
    and `blob` like '%rlms%'
    and length(json_unquote(json_extract(`blob`,'$.request.rlms_number'))) > 2
) a
set o.dealer_number = substring_index(substring_index(a.rlms_number, '*', 2),'*',-1), 
o.return_number = substring_index(substring_index(a.rlms_number, '*', -1),'*',1)
where o.id = a.order_id;

UPDATE orders SET dealer_number = NULL WHERE dealer_number = return_number;

