Advanced SQL question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

OnTheAnvil

Thread Starter
Joined
Sep 25, 2003
Messages
3
I've inherited some code on a program that used to retrieve data from an Access database. Now I'm trying to access the exact same data from SQL 2000 and the SQL code isn't working. Could someone please let me know of a different way of accomplishing this same delete function but with a different SQL string?

--------------------------------
DELETE Findings.* FROM Findings INNER JOIN Report ON Findings.id = Report.id
------------------------------------

The error says, "Error near '*' " if that helps.


Thanks. I've been racking my brain over this for close to two hours.
 
Joined
Feb 12, 2001
Messages
3,420
Just guessing, as I am not a SQL 2000 person. Try removing the "Findings." so it becomes:

DELETE * FROM Findings INNER JOIN Report ON Findings.id = Report.id
 

OnTheAnvil

Thread Starter
Joined
Sep 25, 2003
Messages
3
I appreciate it but I've tried different variations of that. I think the problem is that I'm trying to delete the records from an inner join. It seems like you can only delete entire rows from their original table. I'm trying to think of how to say something like...

DELETE * FROM Findings WHERE (Findings.ID IS IN (Findings INNER JOIN Report ON Findings.id = Report.id)

Obviously "IS IN" is not valid SQL but thats what I'm trying to figure out.


Thanks for the input.
 
Joined
Jun 11, 2003
Messages
152
Hi OTA

I think your problem is because the record set returned from a join that establishes anything other than a one-to-one relationship is usually not updateable. So if more than one record in 'Findings' matches with one or more record in 'Report' the returned record set will be read only.

You could try a query to delete records from 'Findings' using the DCount function as a criterion (e.g. 'WHERE DCOUNT("[id]","Report","[id] = " & [Findings].[id]) > 0' if the field 'id' is numeric) or (e.g. 'WHERE DCOUNT("[id]","Report","[id] = '" & [Findings].[id] & "'") > 0' if the field 'id' is text). I haven't checked the syntax with a fine-toothed comb but it should be pretty near right.

I think though my choice would be to use a sub query in the WHERE clause as follows: DELETE Findings.* FROM Findings WHERE Findings.id IN (SELECT Report.id FROM REPORT;);

This seems to me to be rather more elegant and in the long run has the advantage that you can actually make the sub query quite complex if required without it making the overall SQL too cumbersome. For quickness you could assemble the sub query in the Access QBE grid (if you're using Access) and copy the resultant SQL for pasting into the final query.

You may find it useful to know that you can also negate a sub query used as a WHERE criterion: DELETE Findings.* FROM Findings WHERE Findings.id NOT IN (SELECT Report.id FROM REPORT;);

There may be other (simpler?) ways to handle it that I haven't thought of yet - but whatever your chosen solution I suggest you back up everything before testing!

Good luck with it.

Deej
 
Joined
Jun 11, 2003
Messages
152
Sorry - forgot to disable smilies - it should read:

Hi OTA

I think your problem is because the record set returned from a join that establishes anything other than a one-to-one relationship is usually not updateable. So if more than one record in 'Findings' matches with one or more record in 'Report' the returned record set will be read only.

You could try a query to delete records from 'Findings' using the DCount function as a criterion (e.g. 'WHERE DCOUNT("[id]","Report","[id] = " & [Findings].[id]) > 0' if the field 'id' is numeric) or (e.g. 'WHERE DCOUNT("[id]","Report","[id] = '" & [Findings].[id] & "'") > 0' if the field 'id' is text). I haven't checked the syntax with a fine-toothed comb but it should be pretty near right.

I think though my choice would be to use a sub query in the WHERE clause as follows: DELETE Findings.* FROM Findings WHERE Findings.id IN (SELECT Report.id FROM REPORT;);

This seems to me to be rather more elegant and in the long run has the advantage that you can actually make the sub query quite complex if required without it making the overall SQL too cumbersome. For quickness you could assemble the sub query in the Access QBE grid (if you're using Access) and copy the resultant SQL for pasting into the final query.

You may find it useful to know that you can also negate a sub query used as a WHERE criterion: DELETE Findings.* FROM Findings WHERE Findings.id NOT IN (SELECT Report.id FROM REPORT;);

There may be other (simpler?) ways to handle it that I haven't thought of yet - but whatever your chosen solution I suggest you back up everything before testing!

Good luck with it.

Deej
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top