CODEWITHSUNDEEP
mysql
osmanabi
osmanabi

Reputation: 141

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?

Upvotes: 10

Views: 15509

Answers (5)

fred
fred

Reputation: 319

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.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

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
) ;

Upvotes: 13

Conrad Frix
Conrad Frix

Reputation: 52675

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...)

Upvotes: 1

Kristoffer la Cour
Kristoffer la Cour

Reputation: 2576

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...

Upvotes: 0

dartdog
dartdog

Reputation: 10872

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)

Upvotes: 0

Related Questions