Delete Query using two tables

[SEMich]

Hi,



I would like to delete records that are in one table but not the other, but keep getting a message saying:

that you must pecify the table that contains the records you want to delete.

Here is the sql code:

DELETE ctable_in.vname, ctable_in.longname, ctable_in.section, ctable_in.level, ctable_in.cnum, ctable_in.cmax, ctable_in.ctext, ctable_in.check2, ctable_in.vnameorig, ctable_in.match, cfreqs_in.vname
FROM ctable_in LEFT JOIN cfreqs_in ON (ctable_in.[vname] = cfreqs_in.[vname]) AND (ctable_in.[cnum] = cfreqs_in.[cnum])
WHERE (((ctable_in.section)="h") AND ((ctable_in.ctext) Like "*break*") AND ((cfreqs_in.vname) Is Null))

Basically, I am trying to delete the records in ctable_in that don't have a matching record in cfreqs_in based on the criteria specified in the where statement.

Any suggestions to make this run successfully would be appreciated.

Thanks.

[isladogs]

Try DELETE DISTINCTROW .....

[SEMich]

Hi isladogs,

I tried this and it still got the same message:

DELETE DISTINCTROW ctable_in.vname, ctable_in.longname, ctable_in.section, ctable_in.level, ctable_in.cnum, ctable_in.cmax, ctable_in.ctext, ctable_in.check2, ctable_in.vnameorig, ctable_in.match, cfreqs_in.vname
FROM ctable_in LEFT JOIN cfreqs_in ON (ctable_in.[vname] = cfreqs_in.[vname]) AND (ctable_in.[cnum] = cfreqs_in.[cnum])
WHERE (((ctable_in.section)="h") AND ((ctable_in.ctext) Like "*break*") AND ((cfreqs_in.vname) Is Null));

[davegri]

Probably not the problem, but just in case, LEVEL is an MSACCESS reserved word.

[SEMich]

Thanks, davegri. No luck with removing it from the text.

[ssanfu]

I would like to delete records that are in one table but not the other, ...
<snip>Basically, I am trying to delete the records in ctable_in that don't have a matching record in cfreqs_in based on the criteria specified in the where statement.
<snip>

I was going to suggest changing the "DELETE DISTINCTROW? to "SELECT" to see if any records are selected/returned, but when I reformatted the SQL I got




I would try deleting ", cfreqs_in.vname" from the field list - like this

DELETE DISTINCTROW ctable_in.vname, ctable_in.longname, ctable_in.section, ctable_in.level, ctable_in.cnum, ctable_in.cmax, ctable_in.ctext, ctable_in.check2, ctable_in.vnameorig, ctable_in.match
FROM ctable_in LEFT JOIN cfreqs_in ON (ctable_in.[vname] = cfreqs_in.[vname]) AND (ctable_in.[cnum] = cfreqs_in.[cnum])
WHERE (((ctable_in.section)="h") AND ((ctable_in.ctext) Like "*break*") AND ((cfreqs_in.vname) Is Null));

[SEMich]

Thanks, ssanfu! That did the trick! Stupid me.

[ssanfu]

Happy to help - I've done the same and worse...