用联接更新表? [英] Update a table with join?

问题描述

我正在尝试使用表B中的数据更新表A. 我以为我可以做这样的事

I am trying to update table A with data from table B. I thought I can do something like this

update A 
set A.DISCOUNT = 3 
from INVOICE_ITEMS A
join ITEM_PRICE_QUNTITY B on A.ITEM_PRICE_NO = B.ID
where A.INVOICE_ID = 33

但出现错误 SQL讯息:-104 令牌无效

but getting error SQL Message : -104 Invalid token

有人可以帮助我吗?

推荐答案

使用JOIN不可能做到这一点. Firebird UPDATE语句没有FROM子句. 语法为:

It is not possible to do this with a JOIN. The Firebird UPDATE statement has no FROM clause. The syntax is:

UPDATE {tablename | viewname} [[AS] alias]
   SET col = newval [, col = newval ...]
   [WHERE {search-conditions | CURRENT OF cursorname}]
   [PLAN plan_items]
   [ORDER BY sort_items]
   [ROWS <m> [TO <n>]]
   [RETURNING <values>]

<m>, <n>     ::=  Any expression evaluating to an integer.
<values>     ::=  value_expression [, value_expression ...]
<variables>  ::=  :varname [, :varname ...]

但是与您的示例查询等效的是:

However the equivalent of your example query is:

update INVOICE_ITEMS 
set DISCOUNT = 3 
WHERE EXISTS (SELECT 1 FROM ITEM_PRICE_QUNTITY B WHERE B.ID = ITEM_PRICE_NO)
AND INVOICE_ID = 33

如果要使用其他表中的数据进行更新，则可能需要考虑使用

If you want to update using data from additional tables, you might want to consider using MERGE. In your comment you ask for the equivalent query to do the following with Firebird:

UPDATE B 
SET B.QUANTIY = b.QUANTIY + a.QUANTITY 
FROM ITEM_PRICE_QUNTITY B JOIN INVOICE_ITEMS A ON A.ITEM_PRICE_NO = B.ID 
WHERE A.INVOICE_ID = 33

等效的MERGE语句为:

MERGE INTO ITEM_PRICE_QUNTITY AS B
    USING INVOICE_ITEMS AS A
    ON A.ITEM_PRICE_NO = B.ID AND A.INVOICE_ID = 33
    WHEN MATCHED THEN
        UPDATE SET B.QUANTIY = B.QUANTIY + A.QUANTITY 

这篇关于用联接更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！