1

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 
)
1
  • Fernando, do you get an error from MySQL when you try this? Also, which version of MySQL server are you using?
    – Mike Diehn
    Oct 27, 2014 at 20:35

1 Answer 1

0

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

1
  • 1
    But this statement is semantically different from the original, no?
    – mustaccio
    Mar 8, 2020 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.