Professional Web Applications Themes

DELETE where NOT EXISTS - MySQL

I can successfully perform the query SELECT * FROM `mos_availability` a WHERE NOT EXISTS ( SELECT * FROM mos_users b WHERE a.user = b.id) But DELETE FROM `mos_availability` a WHERE NOT EXISTS ( SELECT * FROM mos_users b WHERE a.user = b.id) Says that I have an SQL syntax error. Can anyone spot what it is for me? TIA Regards Paul...

  1. #1

    Default DELETE where NOT EXISTS

    I can successfully perform the query

    SELECT * FROM `mos_availability` a WHERE NOT EXISTS (

    SELECT *
    FROM mos_users b
    WHERE a.user = b.id)
    But

    DELETE FROM `mos_availability` a WHERE NOT EXISTS (

    SELECT *
    FROM mos_users b
    WHERE a.user = b.id)

    Says that I have an SQL syntax error.

    Can anyone spot what it is for me?

    TIA
    Regards
    Paul


    Paul Lautman Guest

  2. #2

    Default Re: DELETE where NOT EXISTS

    Paul Lautman wrote:
    > Says that I have an SQL syntax error.
    My first guess is that you're using MySQL 4.0, which does not support
    subqueries.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: DELETE where NOT EXISTS

    Paul Lautman wrote:
    > I can successfully perform the query
    Ah, mea culpa, I posted my previous response hastily; obviously you are
    using MySQL 4.1 if the subquery in the SELECT works.

    I'd suggest this rewrite:

    DELETE FROM `mos_availability` a WHERE a.user NOT IN (
    SELECT b.id
    FROM mos_users b

    Or use MySQL's multi-table DELETE syntax:

    DELETE a
    FROM `mos_availability` AS a
    LEFT JOIN `mos_users` AS b ON a.user = b.id
    WHERE b.id IS NULL

    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Re: DELETE where NOT EXISTS

    Bill Karwin wrote:
    > Paul Lautman wrote:
    >> Says that I have an SQL syntax error.
    >
    > My first guess is that you're using MySQL 4.0, which does not support
    > subqueries.
    >
    > Regards,
    > Bill K.
    Surely if it didn't support subqueries, the subquery

    SELECT * FROM `mos_availability` a WHERE NOT EXISTS (

    SELECT *
    FROM mos_users b
    WHERE a.user = b.id)

    would not have worked???


    Paul Lautman Guest

  5. #5

    Default Re: DELETE where NOT EXISTS

    Bill Karwin wrote:
    > Paul Lautman wrote:
    >> I can successfully perform the query
    >
    > Ah, mea culpa, I posted my previous response hastily; obviously you
    > are using MySQL 4.1 if the subquery in the SELECT works.
    >
    > I'd suggest this rewrite:
    >
    > DELETE FROM `mos_availability` a WHERE a.user NOT IN (
    > SELECT b.id
    > FROM mos_users b
    >
    > Or use MySQL's multi-table DELETE syntax:
    >
    > DELETE a
    > FROM `mos_availability` AS a
    > LEFT JOIN `mos_users` AS b ON a.user = b.id
    > WHERE b.id IS NULL
    >
    > Regards,
    > Bill K.
    I realised that I could use the NOT IN syntax, but my experience (at least
    with DB2) is that using NOT EXISTS is far more efficienct than the subselect
    for NOT IN.

    Hopefully the LEFT JOIN syntax is also efficient. I'd love to know why the
    NOT EXISTS one works for a select, but not for a DELETE?


    Paul Lautman Guest

  6. #6

    Default Re: DELETE where NOT EXISTS

    Paul Lautman wrote:
    > I can successfully perform the query
    >
    > SELECT * FROM `mos_availability` a WHERE NOT EXISTS (
    >
    > SELECT *
    > FROM mos_users b
    > WHERE a.user = b.id)
    > But
    >
    > DELETE FROM `mos_availability` a WHERE NOT EXISTS (
    >
    > SELECT *
    > FROM mos_users b
    > WHERE a.user = b.id)
    >
    > Says that I have an SQL syntax error.
    >
    > Can anyone spot what it is for me?
    >
    > TIA
    > Regards
    > Paul
    >
    >
    You can't DELETE from a table you're referencing in the subquery.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  7. #7

    Default Re: DELETE where NOT EXISTS

    use following subquery to delete records

    DELETE a from tbl1 a where a.id NOT IN (SELECT b.id FROM tbl2 b).
    Unregistered Guest

Similar Threads

  1. Delete form - Post data to a table and delete uponsubmit.
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: July 28th, 01:27 PM
  2. Delete button to delete 3D model
    By mindylim in forum Macromedia Director 3D
    Replies: 1
    Last Post: February 18th, 10:47 AM
  3. Delete key doesn't delete when datagrid is bound to a disconnected table
    By Fred Zolar in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: April 1st, 07:47 AM
  4. method name exists, property value exists, calling method fails
    By Phil Powell in forum PHP Development
    Replies: 0
    Last Post: October 27th, 07:34 PM
  5. File Exists
    By Gram in forum ASP
    Replies: 4
    Last Post: August 29th, 03:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139