SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Update Table in MySQL

    I had problem on updating a field of one table which had link up to another table. The structures are like these:

    Table Item_Detail
    Item_ID
    Model_No

    Table Purchase_Item
    PO_No
    Item_ID

    UPDATE Item_Detail SET Model_No = 'xx'
    FROM Purchase_Item WHERE
    Item_Detail.Item_ID = Purchase_Item.Item_ID
    AND PO_No = 'X'

    When I try to update using the above SQL, an error eccour.

    Moreover, my DB is build on MySQL rather than SQL Server, is MySQL not support INNER JOIN command ?
    I had already try the following 2 different SQL command but there is a error msg about syntax error at line 3.

    Update item_detail
    SET model_no = 'xx'
    From item_detail, purchase_item
    Where item_detail.item_ID = purchase_item.item_ID
    AND po_no = 'x'

    UPDATE Item_Detail
    SET Model_No = 'xx'
    FROM Purchase_Item JOIN Item_Detail ON Item_Detail.Item_ID = Purchase_Item.Item_ID
    AND PO_No = 'X'

    Pls help !!

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2002
    Location
    UK
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AFAIK, mysql does not support update (or delete) queries using joins.
    Apparently this is on the todo list for version 4.x

    Here is a possible workaround (worked for me using mysql 3.23.49):

    create a temp table to get rows you want to update from Item_Detail table
    i.e. rows that match your select criteria
    Code:
    CREATE TEMPORARY TABLE temp
    SELECT Item_Detail.*
    FROM Item_Detail, Purchase_Item 
    WHERE Item_Detail.Item_ID = Purchase_Item.Item_ID
    AND Purchase_Item.PO_No = 'X'
    update value in temp table - this will update all rows in temp table
    Code:
    UPDATE temp SET Model_No='xx';
    use replace to replace rows in Item_Detail table with rows from temp table where Item_ID matches.
    **this assumes each Item_ID has unique value**
    if this is not unique the rows from temp will be added to your Item_Detail table
    Code:
    REPLACE Item_Detail
    SELECT * FROM temp;

    it works...but surely theres a better way?
    and as always test first

    hope this helps


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
  •