[RESOLVED] inserting based on results of a subquery select

[spiritssight]

Hello there,

I am now taking the php script that was filtering and changing it all over to sql and no php in it, I have it working except this part

I am trying to have this part insert the remaining records that are still in the table irs_rawdata and have only the EIN put in and also a string, any help would be great, I also want to thank you all that do help for helping as its because of you guys that I am able to move along with the project for the non-profit

I have the following code:

INSERT INTO block (fin, who_blocked) VALUES (irs_rawdata.EIN, 'S-123456789')
WHERE NOT EXISTS
	(
	SELECT * FROM irs_rawdata
	  PNO LIKE '%blind%' OR
	  PNO LIKE '%deaf%' OR
	  NTEE_Code LIKE '%P87%'
	);

I have done it with using * in the select and also with EIN as this is the only field need to be inserted into the block table,

what am I doing wrong as its saying that there is a error near the WHERE NOT EXISTS clause?

Thanks for any help!

Sincerely,
Christopher

[NogDog]

Looks like you are missing a "WHERE" in the sub-query.

[spiritssight]

oops :-)

but it still gives a error saying that it has a error near the where clause

INSERT INTO block (fin, who_blocked) VALUES (irs_rawdata.EIN, 'S-123456789')
WHERE
	(
	SELECT * FROM irs_rawdata
	  WHERE NOT EXISTS
	  PNO LIKE '%blind%' OR
	  PNO LIKE '%deaf%' OR
	  NTEE_Code LIKE '%P87%'
	);

I also have tried:

INSERT INTO block (`fin`, `who_blocked`)
SELECT EIN, 'S-123456789'
FROM irs_rawdata
	WHERE  NOT EXISTS
	  PNO LIKE '%blind%' OR
	  PNO LIKE '%deaf%' OR
	  NTEE_Code LIKE '%P87%';

so what am I doing so wrong? 🙁

Sincerely,
Christopher

[NogDog]

I'm not sure I understand what selection logic you want for the SELECT part, but I don't think the "NOT EXISTS" makes sense where you have it in your last example, which other than that looks like the right syntax.

[spiritssight]

ok what I am trying to do is:

get all the records that are left in the irs_rawdata table that DID NOT match the piece of code that is above this piece so say if there is 100 records and the first query found 36 records and inserted them and then the next query deletes all the records from irs_rawdata that was inserted in to query table

DELETE FROM irs_rawdata
WHERE EXISTS
	(
	SELECT fin
	FROM block
	);

INSERT INTO query SELECT * FROM irs_rawdata
           WHERE
              PNO LIKE '%blind%' OR
              PNO LIKE '%deaf%' OR
              NTEE_Code LIKE '%P87%';

DELETE FROM irs_rawdata
           WHERE
	      PNO LIKE '%blind%' OR
	      PNO LIKE '%deaf%' OR
              NTEE_Code LIKE '%P87%';

INSERT INTO block (fin, who_blocked) VALUES (irs_rawdata.EIN, 'S-123456789')
           WHERE
	   (
	   SELECT * FROM irs_rawdata
           WHERE NOT EXISTS
      	     PNO LIKE '%blind%' OR
	     PNO LIKE '%deaf%' OR
	     NTEE_Code LIKE '%P87%'
	   );
//  not using this next one till I KNOW the rest works correctly
//  this will wrip the table clean because there is nothing in it that needs to be 
//  done now as the rest of the query have did what it need to do successful
DELETE irs_rawdata

Does this give you a better understanding?

Sincerely,
Christopher

[NogDog]

I think you want:

INSERT INTO block (`fin`, `who_blocked`)
  SELECT EIN, 'S-123456789' FROM irs_rawdata
    WHERE  

      PNO NOT LIKE '%blind%' AND
      PNO NOT LIKE '%deaf%' AND
      NTEE_Code NOT LIKE '%P87%';