SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    having trouble with delete query in MySQL

    Im trying to delete some data from a table and keep getting an error. here are the queries i have:


    PHP Code:
    $INSERT="INSERT INTO InactiveSchedule SELECT Schedule.Schkey, Schedule.Empkey, Schedule.LastName, Schedule.FirstName,Schedule.Dept, Schedule.Crskey, Schedule.NoteBook, Schedule.Bydate, Schedule.Compdate, Schedule.Completeyn, Schedule.Scheduledyn, Schedule.AddDate, Schedule.UpdDate    FROM InactiveEmployees, Schedule WHERE (InactiveEmployees.SSN = Schedule.Empkey)";
    $Result=mysql_query($INSERT) OR DIE(mysql_error());


    $DELETE "DELETE FROM Schedule WHERE Schedule.Empkey = InactiveSchedule.Empkey";
    $Result2=mysql_query($DELETE) OR DIE(mysql_error()); 
    Ok the first query works fine, now the second one is supposed to delete data from schedule which was moved to inactive schedule....and i get this error: Unknown table 'InactiveSchedule' in where clause.....how do i reference this table??? thanks for any help??

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in mysql 4+ (see DELETE Syntax) you can delete rows in one table based on a join to another table
    Code:
    DELETE Schedule
      FROM Schedule, InactiveSchedule
     WHERE Schedule.Empkey = InactiveSchedule.Empkey
    i think you could also delete from InactiveSchedule at the same time, if that's required, just by adding it to the list of tables to delete from --
    Code:
    DELETE Schedule, InactiveSchedule
      FROM Schedule, InactiveSchedule
     WHERE Schedule.Empkey = InactiveSchedule.Empkey
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i tried that first one query already cause thats the one I need and it still gives me an error:

    You have an error in your SQL syntax near 'Schedule FROM Schedule, InactiveSchedule WHERE Schedule.Empkey = InactiveEmploye' at line 1

    I thought that should work........im really stuck now.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ... FROM Schedule, InactiveSchedule WHERE Schedule.Empkey = InactiveEmploye'

    table name spelled correctly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah everything is spelled right.....i thought that should of worked

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    show us the CREATE TABLE statements, and the DELETE query...

    ... oh, and confirm version number is >= 4

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    He most likely is on MySQL 3 which does not support joined deletes/updates.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you guys are right........this mysql is version 3...gonna have to get that updated..frustrated over that......ahhhhhhhhhhhhhhhhhhhhhhhhhhhh

  9. #9
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    4* is still Alpha. The latest stable is 3.23.51

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature


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
  •