Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts

    Help needed with simple UPDATE. I keep getting "ZERO ROWS EFFECTED".

    if I run this SELECT
    Code:
    SELECT r.salesOrderID, r.stockID, p.number, s.quantity, r.reserved, s.location, s.manufacturer, s.datecode, s.ConsignmentCode
    FROM Stockline AS s
    LEFT JOIN Reserve AS r USING(stockID)
    LEFT JOIN Part AS p USING(partID)
    WHERE r.salesOrderID=217762
    I get a healthy dataset of 24 records.

    When I try this UPDATE
    Code:
    UPDATE Stockline
    SET consignmentCode=concat("s",consignmentCode)
    WHERE stockID=(
        SELECT stockID FROM Reserve WHERE salesOrderID=217762
    )
    I get "Rows Effected: 0" (and no errors).

    What am I missing?

    ~ Mo

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    In your SELECT, you are LEFT joining with Reserve, which says "return everything on the left (Stockline), even if there's not a match on the right (Reserve)". In your UPDATE, you are making it mandatory to match Stockline with Reserve, and apparently there is no match between the two for salesOrderID 217762.

  • #3
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    I also tried a SELECT including s.stockID, and confirmed that there are records in both tables for each record in the result set.

    I also tried it by adding AND r.reserved=s.quantity to the the WHERE clause, and once again, confirmed matching records in both tables.

    Is there anything else I may be missing?

    ~ Mo

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts

    Smile

    I know in DB2 you'll get an "ambiguous column reference" on your UPDATE statement because you are referring to two diffferent columns named stockID but aren't specifying which tables they come out of. Perhaps put alias names on your tables? (I'm reaching here)

    Also if you do a SELECT using the where clause of your UDPATE, do you get any rows back? MySQL won't update a row if nothing changes and reports 0 rows affected even if rows were found (but your SET statement pretty much rules this theory out).

  • #5
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    1st, I tried alias names early on, to no avail.

    Secondly, I think we've cornered the problem!
    When I did a SELECT using the same WHERE clause as my UPDATE (as you suggested), I rcvd an error #1242 - Subquery returns more than 1 row . AHAH!

    So ... how would I go about doing what I am trying to do?
    (I'm working in PhpMyAdmin.)
    There are somewhere around 25 stockline records selected on this order, as referenced in the Reserve table. I need to change the Consignment Codes for those stocklines.

    ~ Mo

    NOTE: After doing it for this order, I also need to clean up the data in a couple more areas which would use the same procedure.
    Last edited by mOrloff; 04-18-2009 at 01:39 AM. Reason: updated the "NOTE:"

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    Easy. Just change the = to IN.

    Code:
    UPDATE Stockline
    SET consignmentCode=concat("s",consignmentCode)
    WHERE stockID IN (
        SELECT stockID FROM Reserve WHERE salesOrderID=217762
    )

  • #7
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts
    AWESOME!!

    I've got to go, so I'll be trying that tomorrow.

    ~ Mo


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •