主页 / 问题 / 1043507
Accepted
Bakuard
Asked:2020-11-07 22:22:24 +0000 UTC2020-11-07 22:22:24 +0000 UTC

使用另一个表中的值更新一个表

  • 772

再会。

任务：我使用 DBMS H2。有两个表（项目中有更多的表，但我们只会考虑这些）：

CREATE TABLE DISHES (
       id INT AUTO_INCREMENT PRIMARY KEY,
       dish_name VARCHAR_IGNORECASE(256) NOT NULL,
       picture_name VARCHAR(256),
       unit VARCHAR(256),
       price DECIMAL(64,32) DEFAULT 0 CHECK(price >= 0),
       calories DECIMAL(64,32) DEFAULT 0 CHECK(calories >= 0),
       UNIQUE(dish_name)
);

CREATE TABLE DISH_TO_PRODUCTS_COMPOSITION (
       product_id INT NOT NULL,
       dish_id INT NOT NULL,
       amount_product DECIMAL(64,32) DEFAULT 1 CHECK(amount_product > 0),
       FOREIGN KEY (product_id) REFERENCES PRODUCTS(id) ON DELETE CASCADE,
       FOREIGN KEY (dish_id) REFERENCES DISHES(id) ON DELETE CASCADE,
       PRIMARY KEY(product_id, dish_id)
);

和一种观点：

CREATE VIEW DISH_PRICE_CALORIES 
       (dish_id, price, calories) AS 
       SELECT 
       DISH_TO_PRODUCTS_COMPOSITION.dish_id, 
       SUM(PRODUCTS.price * DISH_TO_PRODUCTS_COMPOSITION.amount_product),
       SUM(PRODUCTS.calories * DISH_TO_PRODUCTS_COMPOSITION.amount_product)
       FROM
       DISH_TO_PRODUCTS_COMPOSITION, PRODUCTS 
       WHERE DISH_TO_PRODUCTS_COMPOSITION.product_id = PRODUCTS.id 
       GROUP BY DISH_TO_PRODUCTS_COMPOSITION.dish_id;

需要更新DISHES表，即用DISH_PRICE_CALORIES表中同名列的值更新价格和卡路里列。您需要更新包含具有特定 ID 的产品的菜肴。

我如何尝试解决问题：我创建了一个请求

UPDATE DISHES 
       SET price = DISH_PRICE_CALORIES.price, calories = DISH_PRICE_CALORIES.calories 
       WHERE DISHES.id = DISH_TO_PRODUCTS_COMPOSITION.dish_id AND 
       DISH_PRICE_CALORIES.dish_id = DISH_TO_PRODUCTS_COMPOSITION.dish_id AND 
       DISH_TO_PRODUCTS_COMPOSITION.product_id = ?;

查询本身被创建为 PreparedStatement 对象。

我收到一个文本错误： org.h2.jdbc.JdbcSQLSyntaxErrorException：找不到列“DISH_TO_PRODUCTS_COMPOSITION.DISH_ID”。

问题：如何使用另一张表中的值更新H2数据库中的一张表？我在哪里做错了？

sql

1 个回答

  1. Best Answer

    会有类似的东西

    UPDATE DISHES d
SET (d.price, d.calories) = ( SELECT dc.price, dc.calories 
                              FROM DISH_PRICE_CALORIES dc
                              WHERE d.id = dc.dish_id );

    是的，关于传递的参数

    DISH_TO_PRODUCTS_COMPOSITION.product_id = ?

    完全默默无闻。如果我们更新一道菜，为什么要产品？

    您需要更新包含具有特定 ID 的产品的菜肴

    UPDATE DISHES d
SET (d.price, d.calories) = ( SELECT dc.price, dc.calories 
                              FROM DISH_PRICE_CALORIES dc
                              WHERE d.id = dc.dish_id )
WHERE EXISTS ( SELECT 1
               FROM DISH_TO_PRODUCTS_COMPOSITION dpc
               WHERE d.id = dpc.dish_id
                 AND dpc.product_id = ? );
    • 2

相关问题