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"))

score 0 · Accepted Answer · edited Apr 13 '17 at 12:43

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 !!!

They were actually two seperate queries, I wanted to know if I could merge them together in an update query. But yes the two queries works absolutely fine.. Thanks! — Rey, Apr 01 '15 at 11:21

updating same column as in the select query

1 Answers1

ROOT CAUSE

SUGGESTION

GIVE IT A TRY !!!