1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Advanced SQL question

Discussion in 'Business Applications' started by OnTheAnvil, Sep 25, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. OnTheAnvil

    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.
     
  2. THoey

    THoey

    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
     
  3. OnTheAnvil

    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.
     
  4. deej

    deej

    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
     
  5. deej

    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
     
  6. OnTheAnvil

    OnTheAnvil Thread Starter

    Joined:
    Sep 25, 2003
    Messages:
    3
    Thanks I think that really answered my question. It seems to be working now.
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/167430

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice