Update a table with join?

Question

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?

Novah

just use delimeter ';' at end of the Statement – Mitch

try removing A in A.DISCOUNT – Justiciable

Note: I have removed the sql-server tag: your question is not about SQL Server (a Microsoft product), but about Firebird. Please only add relevant tags to your question – Babylonian

Answer 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 

Babylonian

can i get firebird sql for this query?(reverse version of above query) 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 – Novah

Answer 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

Sonja

this might be a solution as well, I couldn't test your answer with my problem since the question is posted five years ago.. – Novah