updating same column as in the select query

Question

When I try to do this, I get an error saying I cannot use the same table in where select as the column that I am updating.

UPDATE table_name
   SET quantity = 19
 WHERE  productID = 148357
               AND productCost IS NOT NULL
               AND exampleDate1 >=
                      (SELECT min(exampleDate1)
                         FROM table_name
                        WHERE exampleDate1 >=
                                 (SELECT min(exampleDate)
                                    FROM table_name2
                                   WHERE description LIKE "We are true"))

Answer 1

ROOT CAUSE

Look at the following parts of your query

UPDATE table_name (<<-- table_name)
   SET quantity = 19
 WHERE  productID = 148357
               AND productCost IS NOT NULL
               AND exampleDate1 >=
                      (SELECT min(exampleDate1)
                         FROM table_name (<< -- same table_name)
                        WHERE exampleDate1 >=
                                 (SELECT min(exampleDate)
                                    FROM table_name2
                                   WHERE description LIKE "We are true"))

You cannot execute an UPDATE or DELETE query against a table that uses a subquery against that same table. I have discussed this before :

• Feb 22, 2011 : Problem with MySQL subquery
• Jan 23, 2013 : Problem with nested UPDATE queries
• Apr 30, 2013 : UPDATE table based on the same table

SUGGESTION

Rewrite it as two queries

#
# Retrieve Minimum Date into a Session Variable
#
SELECT min(exampleDate1) INTO @MininumDate
FROM table_name
WHERE exampleDate1 >=
(
    SELECT min(exampleDate)
    FROM table_name2
    WHERE description LIKE "We are true"
);
#
# Use Session Variable in the WHERE clause of the UPDATE
#
UPDATE table_name
    SET quantity = 19
    WHERE  productID = 148357
    AND productCost IS NOT NULL
    AND exampleDate1 >= @MininumDate
;

GIVE IT A TRY !!!