Ellis improvement

DELETE hhp
FROM HO_HOTEL_PHOTO hhp
WHERE hhp.VENDOR_COMP_CODE = 910003
AND hhp.HOTEL_CODE in (SELECT distinct hhm.HOTEL_CODE
FROM HO_HOTEL_MASTER hhm
WHERE hhm.COUNTRY_CODE IN ('JP', 'KR', 'VN'))
;

INSERT IGNORE HO_HOTEL_PHOTO
(HOTEL_CODE, PHOTO_URL, PHOTO_CAPTION_EN, PHOTO_CAPTION_KO, PHOTO_CAPTION_JA, PHOTO_CAPTION_VI, PHOTO_CAPTION_ZH, DISPLAY_ORDER, VENDOR_COMP_CODE, FIRST_INSERT_UNO, FIRST_INSERT_DATETIME, LAST_UPDATE_UNO, LAST_UPDATE_DATETIME)
SELECT DISTINCT vvhm.HOTEL_CODE
,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
vvhp.VENDOR_PHOTO_URL
, 'https://pix1.agoda.net'
, 'https://photos031.ohmyhotel.com'
)
, 'https://pix2.agoda.net'
, 'https://photos032.ohmyhotel.com'
)
, 'https://pix3.agoda.net'
, 'https://photos033.ohmyhotel.com'
)
, 'https://pix4.agoda.net'
, 'https://photos034.ohmyhotel.com'
)
, 'https://pix5.agoda.net'
, 'https://photos035.ohmyhotel.com'
)
, 'https://pix6.agoda.net'
, 'https://photos036.ohmyhotel.com'
)
, 'https://pix7.agoda.net'
, 'https://photos037.ohmyhotel.com'
)
, 'https://pix8.agoda.net'
, 'https://photos038.ohmyhotel.com'
) AS ORIGIN_PHOTO_URL
, vvhp.VENDOR_PHOTO_CAPTION_EN AS PHOTO_CAPTION_EN
, vvhp.VENDOR_PHOTO_CAPTION_KO AS PHOTO_CAPTION_KO
, vvhp.VENDOR_PHOTO_CAPTION_JA AS PHOTO_CAPTION_JA
, vvhp.VENDOR_PHOTO_CAPTION_VI AS PHOTO_CAPTION_VI
, vvhp.VENDOR_PHOTO_CAPTION_ZH AS PHOTO_CAPTION_ZH
, vvhp.VENDOR_DISPLAY_ORDER AS DISPLAY_ORDER
, vvhp.VENDOR_COMP_CODE AS VENDOR_COMP_CODE
, 10000 AS FIRST_INSERT_UNO
, NOW AS FIRST_INSERT_DATETIME
, 10000 AS LAST_UPDATE_UNO
, NOW AS LAST_UPDATE_DATETIME

FROM VD_VENDOR_HOTEL_PHOTO vvhp
INNER JOIN VD_VENDOR_HOTEL_MAPPING vvhm
ON vvhp.VENDOR_HOTEL_CODE = vvhm.VENDOR_HOTEL_CODE
AND vvhm.VENDOR_COMP_CODE = 910003
inner join HO_HOTEL_MASTER hhm ON vvhm.HOTEL_CODE = hhm.HOTEL_CODE
WHERE hhm.COUNTRY_CODE IN ('JP', 'KR', 'VN')
and vvhp.VENDOR_COMP_CODE = 910003
and vvhp.VENDOR_PHOTO_URL IS NOT NULL
;

UPDATE HO_HOTEL_MASTER hhm
JOIN (
SELECT
hhp.PHOTO_URL,
hhp.HOTEL_CODE
FROM HO_HOTEL_MASTER AS hhm
INNER JOIN HO_HOTEL_PHOTO AS hhp
ON hhp.HOTEL_CODE = hhm.HOTEL_CODE
AND hhp.DISPLAY_ORDER = (
SELECT
MIN
FROM HO_HOTEL_PHOTO AS sub
WHERE
sub.HOTEL_CODE = hhp.HOTEL_CODE
)
WHERE
hhp.VENDOR_COMP_CODE = 910003
AND hhm.COUNTRY_CODE IN ('JP', 'KR', 'VN')
) AS A ON A.HOTEL_CODE = hhm.HOTEL_CODE

SET hhm.DEFAULT_PHOTO_URL = A.PHOTO_URL,
hhm.LAST_UPDATE_DATETIME = NOW
WHERE hhm.COUNTRY_CODE IN ('JP', 'KR', 'VN');