如何在 MYSQL 中更新/使视图可更新 [英] How to update/ make a view updatable in MYSQL

问题描述

我通过以下方式创建了一个视图:

I have created a view via:

CREATE VIEW product_prices
AS SELECT Name, Price
FROM Products;

而且我需要更新它，使 10 英镑以下的价格比第 3 类的价格高 10%.这些需要在单独的 SELECT 语句中查找.

And I need to update it so prices under £10 are made 10% bigger apart from ones that are in category 3. These need to be looked up in a separate SELECT statement.

到目前为止我已经得到了这段代码，但我收到了 1443 错误，即视图的定义不允许我更新它.如何使它成为可更新的视图?

I have got this code so far but I'm getting the 1443 error that the definition of the view won't let me update it. How do I make it an updatable view?

UPDATE product_prices
SET Price = Price * 1.1
WHERE Name != (SELECT Name FROM Products WHERE Category_ID = 3)
AND Price < 10;

推荐答案

使用 join 而不是 <>:

UPDATE product_prices pp LEFT JOIN
       Products p
       ON p.name = pp.anme and p.Category_ID = 3
    SET pp.Price = pp.Price * 1.1
WHERE p.Name IS NULL AND pp.Price < 10;

问题是对正在更新的表的操作，而不是视图定义.

The issue is the operation on the table being updated, not the view definition.

这篇关于如何在 MYSQL 中更新/使视图可更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！