¿Cómo hacer UPDATE a varios valores al mismo tiempo desde otra tabla de MySQL?

publicado por: Anonymous

Lo que hago es actualizar valores de tabla que coincidan en cuanto al ID con valores de otra tabla. Mi código es muy lento y largo, no había una forma de hacer UPDATE a los 4 valores una vez que se ha encontrado el ID en vez de buscar uno por uno? Tengo este código actualmente:

UPDATE lithuania_customers_2016, lithuania_customers_history

SET

lithuania_customers_2016.crm_status = (
SELECT lithuania_customers_history.crm_status
FROM lithuania_customers_history
WHERE lithuania_customers_2016.customer_db_id = lithuania_customers_history.customer_db_id
ORDER BY crm_upload_date DESC
LIMIT 1
),

lithuania_customers_2016.crm_nad = (
SELECT lithuania_customers_history.crm_nad
FROM lithuania_customers_history
WHERE lithuania_customers_2016.customer_db_id = lithuania_customers_history.customer_db_id
ORDER BY crm_upload_date DESC
LIMIT 1
),


lithuania_customers_2016.crm_account_status = (
SELECT lithuania_customers_history.crm_account_status
FROM lithuania_customers_history
WHERE lithuania_customers_2016.customer_db_id = lithuania_customers_history.customer_db_id
ORDER BY crm_upload_date DESC
LIMIT 1
),


lithuania_customers_2016.crm_notes = (
SELECT lithuania_customers_history.crm_notes
FROM lithuania_customers_history
WHERE lithuania_customers_2016.customer_db_id = lithuania_customers_history.customer_db_id
ORDER BY crm_upload_date DESC
LIMIT 1
)


WHERE lithuania_customers_2016.customer_db_id = lithuania_customers_history.customer_db_id
AND lithuania_customers_history.user_id = '100011';

Os lo agradezco.

solución

Puedes simplicar tu query quitando las subconsultas y creando un UPDATE FROM:

UPDATE lithuania_customers_2016, lithuania_customers_history
SET
    A.crm_status = B.crm_status,
    A.crm_nad = B.crm_nad
    A.crm_account_status = B.crm_account_status
    A.crm_notes = B.crm_notes
FROM lithuania_customers_2016 A, lithuania_customers_history B
    ON lithuania_customers_2016.customer_db_id = lithuania_customers_history.customer_db_id
WHERE B.user_id = '100011';
Respondido por: Anonymous

Leave a Reply

Your email address will not be published. Required fields are marked *