SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Destiny Manager Plebius's Avatar
    Join Date
    Nov 1999
    Posts
    682
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating multiple tables...

    Hey, I'm having a problem getting mysql to update multiple tables at once. I keep getting a syntax error:

    Here's the code I have
    Code:
    	 $db->sql_query("UPDATE "._USERS_TABLE." u, 
    	"._REFERRALS_TABLE." r,
    	"._USERS_TABLE." ru
       SET  u.user_points = u.user_points + 5, 
    	r.deleteok = 1 
       WHERE  u.user_id = r.inviter_id
    	AND ru.user_id = r.user_id 
    	AND ru.user_points > 10
       LIMIT 1") or errmsg($db->sql_error());
    Any suggestions?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    according to this page in the manual,
    Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Destiny Manager Plebius's Avatar
    Join Date
    Nov 1999
    Posts
    682
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I saw that and took it out, still have the problem.

    I think that mysql just doesn't like aliases in updates.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, you could always try it without the aliases ...

    oh, wait, you're updating the same table that you're selecting from ...

    you might just have to do this in two steps
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Destiny Manager Plebius's Avatar
    Join Date
    Nov 1999
    Posts
    682
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep I guess so. Thanks for the assistance


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
  •