CODEWITHSUNDEEP
mysqlsqlselectsql-update
user2396911
user2396911

Reputation: 163

Update WHERE Field IN

I've this working query:

SELECT idVendRigMov
FROM VendRM
WHERE idArtPromo IS NULL AND
      (idArtCategoria NOT IN (SELECT idArtCategoria FROM VendScontiCatEscl WHERE idVendSconti=2) OR idArtCategoria IS NULL) AND idVendTesMov=1

that return a list of "idVendRigMov";

I need to do update on each row of this result, like this:

UPDATE VendRigMov SET Sconto='7' WHERE idVendRigMov IN (result of previous query);

I've tried this:

UPDATE VendRigMov SET Sconto='7' WHERE idVendRigMov IN 
(
SELECT idVendRigMov FROM VendRM WHERE idArtPromo IS NULL AND (idArtCategoria NOT IN (SELECT idArtCategoria FROM VendScontiCatEscl WHERE idVendSconti=2) OR 
idArtCategoria IS NULL) AND idVendTesMov=1
)

but I obtain this error: "The definition of table "VendRM" prevents operation UPDATE on table 'VendRigMov'"

How can I do this query?

Upvotes: 1

Views: 1083

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

VendRM is probably a view that refers to the table VendRigMov. If so, you can fix this problem by using an additional level of subqueries. Your query is:

UPDATE VendRigMov
    SET Sconto = '7'
    WHERE idVendRigMov IN (SELECT idVendRigMov
                           FROM VendRM
                           WHERE idArtPromo IS NULL AND
                                 (idArtCategoria NOT IN (SELECT idArtCategoria
                                                         FROM VendScontiCatEscl
                                                         WHERE idVendSconti=2) OR 
                                                               idArtCategoria IS NULL
                                                        ) AN
                                idVendTesMov=1
                          )

This should work:

UPDATE VendRigMov
    SET Sconto = '7'
    WHERE idVendRigMov IN (select idVendRigMov
                           from (SELECT idVendRigMov
                                 FROM VendRM
                                 WHERE idArtPromo IS NULL AND
                                       (idArtCategoria NOT IN (SELECT idArtCategoria
                                                               FROM VendScontiCatEscl
                                                               WHERE idVendSconti=2) OR 
                                                                     idArtCategoria IS NULL
                                                              ) AN
                                      idVendTesMov=1
                                ) t
                          )

This forces MySQL to instantiate the subquery. When instantiated, you no longer have the problem of the outer-referenced table being immediately accessed in the subquery.

Upvotes: 1

Related Questions