Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
274 views
in Technique[技术] by (71.8m points)

sql - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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 

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...