-1
DELETE FROM `tblinvoices` WHERE userid NOT IN (SELECT f.id FROM `tblclients` f);

I am running the above query to delete invoices where the userid doesn't exist in the client pool.

In theory, each part of this query works individually but when combined "NOT IN" - doesn't work.

I have no idea as to why this is, I have tested it a lot and can't get it to work at all.

When running the below query from tblinvoices:

SELECT * FROM `tblinvoices` WHERE userid = 73;

The query returns correctly, and returns that user's invoices:

SELECT f.id FROM `tblclients` f;

Also runs perfectly fine on it's own, returning all the userids from tblclients.

I AM USING SELECT FOR PREVIEWING CHANGES

Simulating the query I am able to see this returned:

SQL query:

DELETE FROM `tblinvoices` WHERE userid NOT IN (SELECT f.id FROM `tblclients` f)

Matched rows: 0
Charlotte Wells
  • 375
  • 1
  • 2
  • 8

2 Answers2

0

Your query is correct .Please check data types of userid in both tables tblinvoices and tblclients,both should be same data type and check the ids available or notavailable tblinvoices

y durga prasad
  • 1,184
  • 8
  • 11
0

Okay - I found the issue causing this, Maria has a DB setting which doesn't allow NOT IN.

This is normally the case with some Shared Hosting.

Charlotte Wells
  • 375
  • 1
  • 2
  • 8