CODEWITHSUNDEEP
mysqlsql-server
Nelfo
Nelfo

Reputation: 3835

How to translate this MSSQL query using "EXCEPT" to MySQL?

The problem is with "EXCEPT" which is not supported by MySQL. The goal is to find all rows where all columns (except id) are not identical.

SELECT B.*, 'modified'  AS 'changetype'
FROM (
        SELECT * FROM table1 
        EXCEPT
        SELECT * FROM table2 
) S1
INNER JOIN table2  B ON S1.id = B.id;

Upvotes: 1

Views: 298

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is tricky. You're going to have to list out all the columns in MySQL. This is probably the closest to what you want:

select t2.*
from table2 t2
where not exists (select 1
                  from table1 t1
                  where t1.id = t2.id and
                        t1.col1 = t2.col1 and
                        t1.col2 = t2.col2 and
                        . . .
                 );

Upvotes: 1

Related Questions