Update a table with join?

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

but getting error SQL Message : -104 Invalid token

can anyone help me?

Solution 1:

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

The equivalent MERGE statement would be:

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 

Solution 2:

From FB manual, if you are using Firebird 2.0 or above, you can use EXECUTE BLOCK to write a more effective statement:

EXECUTE BLOCK
AS
DECLARE VARIABLE field1 type;
DECLARE VARIABLE field2 type;
-- ...etc.
DECLARE VARIABLE pk type;
BEGIN
   for select pk, field1, field2, ... from src_table
   into :pk, :field1, :field2, ...
   do update dest_table set field1 = :field1, field2 = :field2, -- ...
   where pk = :pk;
END