SQL Delete statement

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • AlanAylett
    #1

    SQL Delete statement

    Hi all

    wondering if anybody could spot anything wrong with this SQL DELETE
    statement, both my Access and SQL skills are not what they used to be
    so i reckon its something simple, any suggestions appreciated.

    -----
    DELETE dbo_SUMBill_New .*, dbo_SUMBill_New .BillAccountNum ber,
    dbo_SUMBill_New .POBReference

    FROM a INNER JOIN dbo_SUMBill_New ON a.POBReference =
    dbo_SUMBill_New .POBReference

    WHERE (((dbo_SUMBill_ New.BillAccount Number)=[a].[BillAccountNumb er])
    AND ((dbo_SUMBill_N ew.POBReference )=[a].[POBReference]));
    -----

    Regards, Alan Aylett
    Tags: None
    • MGFoster
      #2
      Re: SQL Delete statement

      -----BEGIN PGP SIGNED MESSAGE-----
      Hash: SHA1

      Might try these:

      DELETE N.*
      FROM dbo_SUMBill_New As N INNER JOIN A
      ON N.POBReference = A.POBReference
      AND N.BillAccountNu mber = A.BillAccountNu mber

      or

      DELETE *
      FROM dbo_SUMBill_New As N
      WHERE EXISTS (SELECT * FROM A
      WHERE N.POBReference = A.POBReference
      AND N.BillAccountNu mber = A.BillAccountNu mber)

      As a safety precaution change DELETE to SELECT to test - it should show
      you the records that will be selected for deletion.

      These queries should delete the records from dbo_SUMBill_New that have
      the same POBReference and BillAccountNumb er as table A. Is this what
      you want?

      --
      MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
      Oakland, CA (USA)

      -----BEGIN PGP SIGNATURE-----
      Version: PGP for Personal Privacy 5.0
      Charset: noconv

      iQA/AwUBQQ6BWoechKq OuFEgEQJd+gCghL YEGRsn/8WAa0i1pIQ3J19H CAMAnjPc
      +/o8GqH0MdK1H27UE cqgYMwM
      =/7+5
      -----END PGP SIGNATURE-----


      AlanAylett wrote:
      [color=blue]
      > Hi all
      >
      > wondering if anybody could spot anything wrong with this SQL DELETE
      > statement, both my Access and SQL skills are not what they used to be
      > so i reckon its something simple, any suggestions appreciated.
      >
      > -----
      > DELETE dbo_SUMBill_New .*, dbo_SUMBill_New .BillAccountNum ber,
      > dbo_SUMBill_New .POBReference
      >
      > FROM a INNER JOIN dbo_SUMBill_New ON a.POBReference =
      > dbo_SUMBill_New .POBReference
      >
      > WHERE (((dbo_SUMBill_ New.BillAccount Number)=[a].[BillAccountNumb er])
      > AND ((dbo_SUMBill_N ew.POBReference )=[a].[POBReference]));
      > -----
      >
      > Regards, Alan Aylett[/color]

        Comment

        • AlanAylett
          #3
          Re: SQL Delete statement

          Cheers for reply, unfortunately still experiencing problems.

          probably didnt give enough information yesterday, brain was shot from
          long office day.

          my problem is more with access than the SQL, i am trying to run the
          SQL in microsoft access 2000. If i replace the DELETE with SELECT it
          returns all the records which i wish to delete, thus SQL works ok

          However, when i try to execute the DELETE statement i get the error
          message

          'Could not delete from specified tables'

          in a pop up box. The help supplied for this error is the usual ms
          waffle which doesnt give you alot to work with.

          Any access experts, please have a stab in the dark.

          Regards, Alan Aylett

          MGFoster <me@privacy.com > wrote in message news:<FjvPc.632 0$cK.2377@newsr ead2.news.pas.e arthlink.net>.. .[color=blue]
          > -----BEGIN PGP SIGNED MESSAGE-----
          > Hash: SHA1
          >
          > Might try these:
          >
          > DELETE N.*
          > FROM dbo_SUMBill_New As N INNER JOIN A
          > ON N.POBReference = A.POBReference
          > AND N.BillAccountNu mber = A.BillAccountNu mber
          >
          > or
          >
          > DELETE *
          > FROM dbo_SUMBill_New As N
          > WHERE EXISTS (SELECT * FROM A
          > WHERE N.POBReference = A.POBReference
          > AND N.BillAccountNu mber = A.BillAccountNu mber)
          >
          > As a safety precaution change DELETE to SELECT to test - it should show
          > you the records that will be selected for deletion.
          >
          > These queries should delete the records from dbo_SUMBill_New that have
          > the same POBReference and BillAccountNumb er as table A. Is this what
          > you want?
          >
          > --
          > MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
          > Oakland, CA (USA)
          >
          > -----BEGIN PGP SIGNATURE-----
          > Version: PGP for Personal Privacy 5.0
          > Charset: noconv
          >
          > iQA/AwUBQQ6BWoechKq OuFEgEQJd+gCghL YEGRsn/8WAa0i1pIQ3J19H CAMAnjPc
          > +/o8GqH0MdK1H27UE cqgYMwM
          > =/7+5
          > -----END PGP SIGNATURE-----
          >
          >
          > AlanAylett wrote:
          >[color=green]
          > > Hi all
          > >
          > > wondering if anybody could spot anything wrong with this SQL DELETE
          > > statement, both my Access and SQL skills are not what they used to be
          > > so i reckon its something simple, any suggestions appreciated.
          > >
          > > -----
          > > DELETE dbo_SUMBill_New .*, dbo_SUMBill_New .BillAccountNum ber,
          > > dbo_SUMBill_New .POBReference
          > >
          > > FROM a INNER JOIN dbo_SUMBill_New ON a.POBReference =
          > > dbo_SUMBill_New .POBReference
          > >
          > > WHERE (((dbo_SUMBill_ New.BillAccount Number)=[a].[BillAccountNumb er])
          > > AND ((dbo_SUMBill_N ew.POBReference )=[a].[POBReference]));
          > > -----
          > >
          > > Regards, Alan Aylett[/color][/color]

            Comment

            • MGFoster
              #4
              Re: SQL Delete statement

              -----BEGIN PGP SIGNED MESSAGE-----
              Hash: SHA1

              Just a guess: Do you have permission to delete on the MS SQL table?
              Check the Permissions on the table you are trying to delete on. Use the
              SQL Enterprise Manager.

              --
              MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
              Oakland, CA (USA)

              -----BEGIN PGP SIGNATURE-----
              Version: PGP for Personal Privacy 5.0
              Charset: noconv

              iQA/AwUBQQ/TFoechKqOuFEgEQ IZLACfeNl0Xb7tK 1ajh8VY/ZubBVqskj8AnjW+
              3kJBUfcY3r+UflJ sQO6GwfF4
              =PF+N
              -----END PGP SIGNATURE-----


              AlanAylett wrote:
              [color=blue]
              > Cheers for reply, unfortunately still experiencing problems.
              >
              > probably didnt give enough information yesterday, brain was shot from
              > long office day.
              >
              > my problem is more with access than the SQL, i am trying to run the
              > SQL in microsoft access 2000. If i replace the DELETE with SELECT it
              > returns all the records which i wish to delete, thus SQL works ok
              >
              > However, when i try to execute the DELETE statement i get the error
              > message
              >
              > 'Could not delete from specified tables'
              >
              > in a pop up box. The help supplied for this error is the usual ms
              > waffle which doesnt give you alot to work with.
              >
              > Any access experts, please have a stab in the dark.
              >
              > Regards, Alan Aylett
              >
              > MGFoster <me@privacy.com > wrote in message news:<FjvPc.632 0$cK.2377@newsr ead2.news.pas.e arthlink.net>.. .
              >[color=green]
              >>-----BEGIN PGP SIGNED MESSAGE-----
              >>Hash: SHA1
              >>
              >>Might try these:
              >>
              >>DELETE N.*
              >>FROM dbo_SUMBill_New As N INNER JOIN A
              >> ON N.POBReference = A.POBReference
              >> AND N.BillAccountNu mber = A.BillAccountNu mber
              >>
              >>or
              >>
              >>DELETE *
              >>FROM dbo_SUMBill_New As N
              >>WHERE EXISTS (SELECT * FROM A
              >> WHERE N.POBReference = A.POBReference
              >> AND N.BillAccountNu mber = A.BillAccountNu mber)
              >>
              >>As a safety precaution change DELETE to SELECT to test - it should show
              >>you the records that will be selected for deletion.
              >>
              >>These queries should delete the records from dbo_SUMBill_New that have
              >>the same POBReference and BillAccountNumb er as table A. Is this what
              >>you want?
              >>
              >>--
              >>MGFoster:::mg f00 <at> earthlink <decimal-point> net
              >>Oakland, CA (USA)[/color][/color]

                Comment

                • Nicole
                  #5
                  Re: SQL Delete statement

                  > my problem is more with access than the SQL, i am trying to run the[color=blue]
                  > SQL in microsoft access 2000. If i replace the DELETE with SELECT it
                  > returns all the records which i wish to delete, thus SQL works ok
                  >
                  > However, when i try to execute the DELETE statement i get the error
                  > message
                  >
                  > 'Could not delete from specified tables'
                  >
                  > in a pop up box. The help supplied for this error is the usual ms
                  > waffle which doesnt give you alot to work with.[/color]

                  Do you have any relationships with delete contraints that might be interfering?

                    Comment

                    Previous template Next
                    Working...