#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    Rely on multiple tables inside a nested select and a delete MySQL statement


    I am trying to write a dual MySQL query which archives the content of a table to another table, then deletes the original row in the original table.

    I have the base query working just fine in other areas. The query below relies on a second table to help select which rows need to be archived & deleted.

    I am getting a syntax error where the AS appears in the nested select of the first query and near the AS in the delete query.

    I have researched and researched and tried a bunch of different code combos, but I can't get the queries working. The queries are written in PHP using PDO, so please ignore the PDO tags, they are not the problem.

    Code:
    INSERT INTO users_sessions_exercise_t_ws_correlation_archive (reset_id, correlation_id, occupation_id, talent_id, style_id, ip_address, timestamp, timestamp_archived) SELECT :reset_id, usetwc.correlation_id, usetwc.occupation_id, usetwc.talent_id, usetwc.style_id, usetwc.ip_address, usetwc.timestamp, :timestamp FROM users_sessions_exercise_t_ws_correlation AS usetwc JOIN users_sessions_exercise_occupations AS useo ON useo.occupation_id = usetwc.occupation_id AND useo.best_fit IS NULL WHERE usetwc.user_id = :user_id;
    Code:
    DELETE FROM users_sessions_exercise_t_ws_correlation AS usetwc JOIN users_sessions_exercise_occupations AS useo ON useo.occupation_id = usetwc.occupation_id AND useo.best_fit IS NULL WHERE usetwc.user_id = :user_id;
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    Someone else suggested that I implement this as a BEFORE DELETE trigger. I have done some research and think I will implement this in the future, but first, I would like to get the queries above working so I can understand my issue.

IMN logo majestic logo threadwatch logo seochat tools logo