MySql Subtract a table from another

I have 开发者_运维技巧two tables: A contains all the data, table B created from A selecting %25 of its data randomly. So A and B have exact same columns. Also there is no unique column.

What I want to do is subtract B from A. Any ideas?

To view all rows in A except those in B:

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

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

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

I have a very similar requirement to you except that for mine, B is just a subset 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 would need to specify the same condition as doing an inner join on A and B.

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

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

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

I'd highly recommend making a ID column 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 it only works for one row...

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