...

View Full Version : Help needed with simple UPDATE. I keep getting "ZERO ROWS EFFECTED".



mOrloff
04-17-2009, 10:22 PM
if I run this SELECT

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

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

Fumigator
04-17-2009, 10:51 PM
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.

mOrloff
04-17-2009, 11:53 PM
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

Fumigator
04-18-2009, 12:38 AM
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).

mOrloff
04-18-2009, 01:09 AM
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.

Old Pedant
04-18-2009, 01:53 AM
Easy. Just change the = to IN.



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

mOrloff
04-18-2009, 02:25 AM
AWESOME!!

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

~ Mo



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum