[ACCEPTED]-MySql Subtract a table from another-mysql

Accepted answer

Score: 13

To view all rows in A except those in B:

SELECT * FROM A
WHERE (field1, field2, ..., fieldN) NOT IN
( SELECT *
  FROM B
) ;

To 2 actually delete from table A the rows that 1 are in B:

DELETE FROM A
WHERE (field1, field2, ..., fieldN) IN
( SELECT *
  FROM B
) ;

Score: 2

I have a very similar requirement to you 4 except that for mine, B is just a subset 3 of A. If you're still looking for an answer:

SELECT * FROM A WHERE NOT EXIST 
 (SELECT * FROM B WHERE A.field1 = B.field1 AND A.field2 = B.field2 etc)

You 2 would need to specify the same condition 1 as doing an inner join on A and B.

Score: 1

Given that you're comparing multiple fields 3 you'll need to either use exists or join. since 2 you're looking to delete its easier yi just 1 use exists.

        delete from
        Tablea
        Where
         Exists(
              Select 1 
               from tableb
              where tablea.fielda = tableb.fielda 
                    And tablea.fieldb = Tableb.fieldb
                   And...)

Score: 0

You need to create a unique key (it can 3 just be a sequential number)on the original 2 table and the you can select matched or 1 unmatched records ( the 25% or the inverse)

Score: 0

I'd highly recommend making a ID column 3 with auto increment, ~~but if you can't just do:~~

~~DELETE FROM a WHERE a.c1 = (SELECT c1 FROM b) AND a.c2 = (SELECT c2 FROM b) AND a.c3 = (SELECT c3 FROM b)~~

Sorry, just realized 2 it only works for one row...

Well, then the 1 only thing i got is making an ID column, sorry...

Source: stackoverflow.com

[ACCEPTED]-MySql Subtract a table from another-mysql

Best way to archive live MySQL database

Foreign keys in MySQL?

Store GUID in MySQL from C#

MySQL backup and restore from command line

Creating your own TinyURL

What is the best way to bind decimal / double / float values with PDO in PHP?

Unique constraint that allows empty values in MySQL

Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?

How do I view the warning(s) after a SELECT query using the MySQL command line?

Java, MySQL: Is there a way to embed a MySQL server with a Java program?