SQL help needed

[dgillz]

The following select statement works perfectly. I am running this to review records before deletion. However when I change the 'SELECT *' to 'DELETE', I cannot get this sql to run at all. Any input appreciated:

SELECT *
FROM         IMDISFIL_SQL LEFT OUTER JOIN AROPNFIL_SQL ON IMDISFIL_SQL.doc_no = AROPNFIL_SQL.doc_no

WHERE     (IMDISFIL_SQL.dist_dt BETWEEN 20050501 AND 20050531) AND 
	  (IMDISFIL_SQL.pkg_id = 'OE') AND
	  (AROPNFIL_SQL.cus_no IS NULL)

The above code will not make much sense as the cus_no should never be null, but I am trying to write some SQL to clean up the database. Any help welcome.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[crystalreporting]

Don,

Are you trying to delete from both AROPNFIL and IMDISFIL? The problem with simply changing the SELECT to DELETE is that SQL doesn't then know which table to remove records from. Try replacing the 'SELECT *' with 'DELETE IMDISFIL_SQL'. This assumes you're wanting to delete IMDISFIL records only..

Oh - and make a backup copy first (like you wouldn't already).

Peter.

Peter Shirley

http://www.linkcorp.com

 

[dgillz]

Peter,

Yes I am trying to delete IMDISFIL only (and yes I made a backup).

I have solved this with a little help from Andy Baldwin as follows:

DELETE from IMDISFIL_SQL
WHERE id_no in (SELECT id_no FROM IMDISFIL_SQL LEFT OUTER JOIN AROPNFIL_SQL ON IMDISFIL_SQL.doc_no = AROPNFIL_SQL.doc_no

WHERE (IMDISFIL_SQL.dist_dt BETWEEN 20050501 AND 20050531) AND (IMDISFIL_SQL.pkg_id = 'OE') AND(AROPNFIL_SQL.cus_no IS NULL))

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz