Translate SQL Server update statement to MySQL

Posted on

Question :

I have a development environment with MySQL and a production environment with SQL Server.

This update statement works with SQL Server:

UPDATE THIS 
SET <list_of_column_assignments>  
FROM <table_name> THIS  
JOIN ( VALUES (<csv1>, <csv2>,...) ) VALS ( <column_names> ) 
ON <list_of_primary_keys_comparison>

How do I translate this to a MySQL statement?

I’d like to have the same code for both environments (if possible).

Example that works on SQL Server:

UPDATE THIS 
SET THIS.DSEndGeoSys = VALS.DSEndGeoSys, 
    THIS.DSPrecisaoGeoSys = VALS.DSPrecisaoGeoSys, 
    THIS.NRLatGeoSys = VALS.NRLatGeoSys, 
    THIS.NRLngGeoSys = VALS.NRLngGeoSys 
FROM TBEstabelecimento THIS 
JOIN
(
    VALUES 
        (
            'Icém - SP, 15460-000, República Federativa do Brasil', 
            'APROXIMADA', '06291846001771', -20.4053212, -49.1625989, '1'
        )
) VALS ( DSEndGeoSys, DSPrecisaoGeoSys, NRCGCCEICPF, NRLatGeoSys, NRLngGeoSys, TPInscricao ) 
ON 
( 
    THIS.TPInscricao = VALS.TPInscricao 
    AND THIS.NRCGCCEICPF = VALS.NRCGCCEICPF 
)

Answer :

MySQL requires a distinct approach: An INSERT with ON DUPLICATE KEY UPDATE.
Example above works like this:

INSERT INTO TBEstabelecimento ( 
    DSEndGeoSys, DSPrecisaoGeoSys, NRCGCCEICPF, NRLatGeoSys, NRLngGeoSys, TPInscricao  
) 
VALUES  (   'Icém - SP, 15460-000, República Federativa do Brasil', 
            'APROXIMADA', '06291846001771', -20.4053212, -49.1625989, '1'
) ON DUPLICATE KEY UPDATE     
DSEndGeoSys = VALUES(DSEndGeoSys), 
DSPrecisaoGeoSys = VALUES(DSPrecisaoGeoSys), 
NRCGCCEICPF = VALUES(NRCGCCEICPF), 
NRLatGeoSys = VALUES(NRLatGeoSys), 
NRLngGeoSys = VALUES(NRLngGeoSys),
TPInscricao = VALUES(TPInscricao) 

Of course, the big benefit of such approach is when you have several VALUES like (csv1), (csv2), ... so you can do the whole update with only one command

Leave a Reply

Your email address will not be published.