Replace substring from an table with an stringified value of another table-CodePudding

So I have 2 tables and one contains an string with an ID I want to replace with an string by another table.
I came up with this SQL, which should work, but it seems like an LEFT JOIN isn't allowed in this case.

UPDATE sales_channel_api_context AS api
SET api.payload = REPLACE(
    api.payload,
    SUBSTRING(
        api.payload,
        LOCATE('paymentMethodId":"', api.payload) 18,
        32
    ),
    LOWER(HEX(c.default_payment_method_id))
)
LEFT JOIN customer AS c
ON c.id = api.customer_id
WHERE api.payload LIKE '%paymentMethodId%' AND api.customer_id IS NOT NULL;

Does anyone know an SQL Query that does exactly this, without creating another table?
An temp table can be used but an new permanent table is no solution.

CodePudding user response：

ChatGPT gave me a working solution and it is as follow:

UPDATE sales_channel_api_context 
JOIN customer c ON c.id = sales_channel_api_context.customer_id
SET payload = 
    CASE 
    WHEN payload LIKE '%paymentMethodId%' THEN
        REPLACE(
            payload,
            SUBSTRING(
                payload,
                LOCATE('paymentMethodId":"', payload)   18,
                32
            ),
            LOWER(HEX(c.default_payment_method_id))
        )
    ELSE payload
    END
WHERE  sales_channel_api_context.customer_id IS NOT NULL;