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

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0

    Unhappy Mysql problem with inner join


    Hello everybody,

    I have a Joomla website and trying to update the category for some articles, based on tag selection.

    There are two tables: table_items and table_tags

    table_items has two columns: id and categoryID
    while table_tags has two columns: tagID and itemID

    These two tables are connected through id and itemID.

    I have the following sql query

    UPDATE `table_items`
    SET `id`=24
    FROM `table_items`
    INNER JOIN `table_tags`
    ON `table_items`.`id`=`table_tags`.`itemID`
    WHERE (`table_items`.`categoryID`=5) AND (`table_tags`.`tagID`=63);

    ...but I get this error:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `table_items` INNER JOIN `table_tags` ON `table_items`.`id`' at line 3

    Any help will be highly appreciated! Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by astraxan
    Code:
    UPDATE `table_items` 
    SET `id`=24 
    FROM `table_items` 
    INNER JOIN `table_tags` ...
    the error message tells you exactly where the sql error is -- at the word FROM

    so you cannot say UPDATE... SET... FROM... (which is actually microsoft sql server syntax)

    mysql syntax is slightly different

    could i ask you to look it up in the mysql manual?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0
    I actually just found solution without using INNER JOIN! Anyway thanks for the help!

    UPDATE `table_items`
    SET `categoryID` = 24
    WHERE EXISTS
    ( SELECT `itemID` FROM `table_tags`
    WHERE `table_tags`.`tagID`=63
    AND `table_items`.`id`=`table_tags`.`itemID`
    AND `catid` = 5 )

IMN logo majestic logo threadwatch logo seochat tools logo