Oracle

根據另一個表的內容更新一個表中的值

  • September 18, 2017

軟體名稱：Oracle SQL Developer 版本 17.2.0.188

我有三個表：orders、order_details和products。

我想執行一個查詢以根據 in 在表中進行更新，units_in_stock其中products關聯的行已被標記為已發貨，並且有一個發貨日期。quantity``order_details``orders

這是我嘗試過的：

UPDATE Products SET (Products.Unit_In_Stock = Products.Unit_In_Stock - Order_Details.Quantity)
INNER JOIN (
   Orders INNER JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID
   ) ON Products.Product_ID = Order_Details.Product_ID
WHERE (
       ((Orders.Shipped_Date) IS NOT NULL)
       AND ((Orders.This_Order_Was_Shipped) = "YES")
       );

當我執行它時，我得到：

Error at Command Line : 1 Column : 45

第 1 行，第 45 列是第一個語句中的等號

Products.Unit_In_Stock = Products.Unit_In_Stock - Order_Details.Quantity

你能幫我搞定這個工作嗎？

假設您的情況與此類似：

-- tables for testing
create table products
as
select 
 1 as product_id
, 100 as unit_in_stock
from dual ; 

create table orders
as
select 
 100 as order_id 
, 'YES' as this_order_was_shipped
, to_date('2017-09-14', 'YYYY-MM-DD') as shipped_date 
from dual;

create table order_details
as
select
 100 as order_id
, 1 as product_id
, 25 as quantity
from dual;

這些表現在包含以下行：

select * from products;

PRODUCT_ID  UNIT_IN_STOCK
1           100

select * from orders;

ORDER_ID  THIS_ORDER_WAS_SHIPPED  SHIPPED_DATE
100       YES                     14-SEP-17

select * from order_details;

ORDER_ID  PRODUCT_ID  QUANTITY
100       1           25

更新可能看起來像…

update products 
set  products.unit_in_stock = (
 select 
   products.unit_in_stock - OD.Quantity
 from orders O
   join order_details OD on O.order_id = OD.order_id
 where O.shipped_date is not null
   and O.this_order_was_shipped = 'YES'
) 
where product_id = 1;

執行 UPDATE 後，PRODUCTS 表包含：

select * from products;
PRODUCT_ID  UNIT_IN_STOCK
1           75

請參閱此處的dbfiddle 。

其他問題和答案：

1) 你寫的程式碼有 OD.Quantity 而不是 Orders.Quantity。程序如何知道 OD.Quantity 是 Orders.Quantity ？

在我的範例中，訂購產品的“數量”儲存在 OrderDetails 表中。（ORDERS 表不包含數量）。

2）您使用 O 而不是 Orders。程序如何知道 O 是 Orders ？

表別名。當您編寫諸如 SELECT … FROM mytable M … 之類的內容時，字母 M 可以用作表的“短名稱”/別名。通常，這會使查詢更容易編碼（和閱讀）。

3) 當我使用 Orders 和 Order_Details 而不是 O 和 OD 替換您的程式碼時，出現錯誤。這是為什麼 ？

我不知道 - 因為您沒有在 dbfiddle 上（或問題本身）放置任何範例。但是，如果您將所有 Os 和 OD 替換為完整的表名，它應該可以工作。請參閱此處的dbfiddle 。請注意，UPDATE 也有一個 WHERE 子句（這是必需的，否則PRODUCTS 表中的所有行都會被更新）。

聽起來您對 Stefan 提供的查詢仍有一些問題。當您在他的測試案例之外擴展時，存在潛在的問題。

如所寫，他的查詢不處理：

  • 將order_details行綁定到右行products，
  • 調整, 和order_details時應使用其中的多行units_in_stock
  • 沒有order_details行可以更新特定行的情況products。

這是他處理這些問題的查詢的修改版本：

update products 
set  products.unit_in_stock = products.unit_in_stock - NVL( (
 select 
   SUM(OD.Quantity)
 from orders O
   join order_details OD on O.order_id = OD.order_id
 where O.shipped_date is not null
   and O.this_order_was_shipped = 'YES'
   and OD.product_id = products.product_id
) , 0)
;

而且，這是相應的dbfiddle 連結

筆記：

  • 我通過採用of涵蓋了從order_details給定的多行的可能性。product_id``SUM``OD.quantity
  • 我通過檢查我們是否從子查詢中返回 NULL 值並用 0 替換 NULL 來涵蓋 aproduct_id沒有任何匹配項的可能性。
  • 我在子查詢的WHERE子句中添加了一個檢查，以確保對於 中的任何給定行products，我們只查看order_details相同的product_id.

**重要提示：**上面的程式碼並沒有阻止行order_details被用於products.units_in_stock 多次更新。您不應按原樣多次應用它。

要解決此問題，您需要執行以下操作之一：

  • 添加一個UPDATE觸發器來orders檢查訂單是否已更改，以使其之前不滿足兩個條件（標記為已發貨，並具有發貨日期），現在則滿足；如果是，products.units_in_stock則從所有相關order_details記錄中更新。如果訂單最初可以添加為已發貨，並帶有發貨日期，那麼您也需要在INSERT觸發器中使用相同的程式碼。（請注意，即使這也不足以保護事物，如果this_order_was_shipped可以更改，或者shipped_date可以設置回 NULL。理想情況下，如果發生這種情況，您需要反轉該過程，將quantity記錄order_details恢復為products.units_in_stock）；
  • orders設置權限，使其order_details只能通過儲存過程插入或修改，並管理儲存過程中的更改（有些人不喜歡觸發器）；要麼
  • orders向or表添加某種標誌order_details，以指示項目已從 中調整products.units_in_stock，更新查詢以忽略帶有標誌的行，並在執行此查詢時設置標誌。

關於您的後續問題：

  1. 你寫的程式碼有 OD.Quantity 而不是 Orders.Quantity。程序如何知道 OD.Quantity 是 Orders.Quantity ？

2）您使用 O 而不是 Orders。程序如何知道 O 是 Orders ？

  1. 當我使用 Orders 和 Order_Details 而不是 O 和 OD 替換您的程式碼時，出現錯誤。這是為什麼 ？

該FROM子句建立O為 的別名Orders和OD的別名Order_Details。

因此，該FROM子句告訴引擎OD.Quantity是Order_Deteails.Quantity（Orders.Quantity順便說一句，不是），這O意味著Orders。

如果你需要在一個FROM子句中兩次包含同一個表，別名是絕對必要的。假設您的訂單有兩個員工 ID 欄位 - 一個用於接受訂單的人，一個用於取消訂單的人。如果您需要這兩個員工的姓名，則需要將該表加入Orders兩次 - 例如：

SELECT O.orderno
     ,Employees.emp_name as Took_Order
     ,cancel_emp.emp_name as Took_Cancel
 FROM Orders O
        INNER JOIN Employees ON (O.order_taken_by = Employees.emp_id)
        LEFT  JOIN Employees cancel_emp ON (O.cancel_taken_by = cancel_emp.emp_id)
;

沒有單獨的名稱cancel_emp，引擎如何知道將哪個emp_name放在哪裡？

如果您保留了別名，但嘗試在其他地方使用原始表名，如下所示：

update products 
set  products.unit_in_stock = (
 select 
   products.unit_in_stock - order_details.Quantity
 from orders O
   join order_details OD on orders.order_id = order_details.order_id
 where orders.shipped_date is not null
   and orders.this_order_was_shipped = 'YES'
   and OD.product_id = products.product_id
)
where product_id = 1;

那麼問題可能是由於這樣一個事實，一旦有了別名，Oracle 就會期望您只使用別名。如果您告訴它Orders應該將表引用為O，然後嘗試在Orders某個地方使用使用者，它會認為這是一個不同的表，並且無法辨識它。如果您不想使用別名，請確保將其放在FROM子句中：

update products 
set  products.unit_in_stock = (
 select 
   products.unit_in_stock - order_details.Quantity
 from orders
   join order_details on orders.order_id = order_details.order_id
 where orders.shipped_date is not null
   and orders.this_order_was_shipped = 'YES'
   and order_details.product_id = products.product_id
)
where product_id = 1;

然後，一切都應該按預期工作。

引用自：https://dba.stackexchange.com/questions/185954

相關問答

Oracle

Oracle 更新左連接

  • August 9, 2021
檢視問答
Oracle

Oracle：非鍵保留表應該是

  • June 21, 2019
檢視問答
Oracle

用另一個表的值更新列

  • September 17, 2017
檢視問答
Oracle

使用聯接和 where exists 查詢更新 oracle SQL 中的記錄

  • May 25, 2017
檢視問答
Mysql

通過自我 JOIN 和 GROUP BY 更新表

  • December 29, 2021
檢視問答
Oracle

是否可以在不創建儲存過程的情況下在 SQL 中進行循環？

  • September 12, 2021
檢視問答