SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problem with multiple delete /left join query

    Alright I asked this question months ago and it worked fine (or so i thought). But I've changed a lot of things on my site and i'm just noticing a problem. I'm using a version of mysql 5.09 beta. Here is what i'm trying to do.

    I have a table that holds information for users not logged into this site
    it looks like this
    mysql> describe session_users;
    +-------------+-------------+------+-----+-------------------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +-------------+-------------+------+-----+-------------------+----------------+
    | session_id | varchar(32) | NO | MUL | | |
    | session_key | varchar(32) | NO | | | |
    | last_access | timestamp | YES | | CURRENT_TIMESTAMP | |
    | id | int(11) | NO | PRI | NULL | auto_increment |
    | remote_addr | varchar(32) | NO | | | |
    +-------------+-------------+------+-----+-------------------+----------------+
    5 rows in set (0.01 sec)


    then i have a table called session_music that looks like this

    mysql> describe session_music;
    +---------+----------------------------------------------------------------------------+------+-----+---------+-------------- --+
    | Field | Type | Null | Key | Default | Extra |
    +---------+----------------------------------------------------------------------------+------+-----+---------+-------------- --+
    | user_id | varchar(32) | NO | MUL | | |
    | song_id | int(10) | NO | | 0 | |
    | special | set('First','Father Daughter','Mother Son','Money','Wedding Party','Line') | NO | | | |
    | id | int(11) | NO | PRI | NULL | auto_incremen t |
    +---------+----------------------------------------------------------------------------+------+-----+---------+-------------- --+

    now when a user visits the site without logging in, this is where the information for that session user is held. If he picks a song its put in session music using the id from session_user table.

    I want to delete entries from this table based on when the last time the last access time occurred for a user, and also from the session_music table if there are any entries to delete (left join). this is the query i had
    Code:
    DELETE session_users, session_music FROM session_users AS su
    					LEFT OUTER JOIN session_music AS sm
    					ON su.id=sm.user_id
    					WHERE su.last_access < subtime(now(), '01:00:00.0')
    i tried running this from the command line and it fails, and same with on the website.. I get an error that says
    "ERROR 1109 (42S02): Unknown table 'session_users' in MULTI DELETE
    "
    I cant figure out why its saying that. the table is there.. i can select from it and everything else.. if i change the beginning portion around to DELETE session_music, session_users

    then it says unknown table session_music
    ALso, i am just noticing while writing this my mysql session keeps aborting.. i have no clue why, maybe its from copying and pasting from there.. I have no clue.. but looks like this might just be a bug, any ideas?

    and one more thing, is a left join just a shorter way of writing left outer join, or are they slightly different?

  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)
    try it without the table aliases

    the keyword OUTER is optional
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •