#1
  1. No Profile Picture
    competitions at lottos.com.au
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    255
    Rep Power
    14

    DELETE issue after MySQL version change


    MySQL upgraded to 5.6.xx and previous DELETE statement no longer runs:

    Code:
    DELETE table_fred.* as f FROM table_fred f, table_tom t WHERE f.tid = t.tid AND t.fid not IN ( 6, 7, 8 ) AND f.type IN('h', 'k');

    I could do a

    Code:
    SELECT f.tid FROM table_fred f LEFT JOIN table_tom t ON f.tid = t.tid WHERE t.fid not IN ( 6, 7, 8 ) AND f.type IN('h', 'k')");
    while.... {
          DELETE FROM table_fred WHERE tid = 'fred[tid]' 
          }
    but would be interested to know what in the MySQL upgrade has put a stop to the top statement and hints on whether it can be fixed rather than running multiple statements.

    Thank you for your advice and expertise.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,656
    Rep Power
    4288
    try this --
    Code:
    DELETE table_fred
      FROM table_fred f
    INNER
      JOIN table_tom t 
        ON t.tid = f.tid 
       AND t.fid not IN ( 6, 7, 8 ) 
     WHERE f.type IN ('h', 'k')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    competitions at lottos.com.au
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    255
    Rep Power
    14
    Originally Posted by r937
    try this --
    Code:
    DELETE table_fred
      FROM table_fred f
    INNER
      JOIN table_tom t 
        ON t.tid = f.tid 
       AND t.fid not IN ( 6, 7, 8 ) 
     WHERE f.type IN ('h', 'k')

    Thank you!

    I thought I'd do a test run first so I replaced DELETE table_fred with SELECT table_fred and it errored with a
    "Unknown column 'table_fred' in 'field list'"

    I then tried it with a SELECT * and it appeared to select the correct records, so I'm curious now as to why a

    Code:
    DELETE table_fred
      FROM table_fred f...
    should work and a

    Code:
    SELECT table_fred
      FROM table_fred f...
    doesn't?

    [edit - update
    ]
    Actually, I ran an
    Code:
    EXPLAIN DELETE table_fred
      FROM table_fred f...
    and that errored with an '#1109 - Unknown table 'table_fred' in MULTI DELETE' too, so I guess there is an issue with the statement.
    Last edited by lottos; March 9th, 2017 at 04:56 AM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,656
    Rep Power
    4288
    weird... try this --
    Code:
    DELETE f
      FROM table_fred f
    INNER
      JOIN table_tom t 
        ON t.tid = f.tid 
       AND t.fid not IN ( 6, 7, 8 ) 
     WHERE f.type IN ('h', 'k')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    competitions at lottos.com.au
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    255
    Rep Power
    14
    Thank you once again - that appeared to work.

IMN logo majestic logo threadwatch logo seochat tools logo