UPDATE INNER JOIN

[Mauricio Ramos]

Hello, good afternoon. I have a problem executing the following script

update pa SET pa.price = '300' from prices_items pa
inner join prices_cli_cli pc on (pc.price_company_id=pa.price_company_id)
inner join claves_articulos ca on (ca.article_id=pa.article_id)
WHERE ca.clave_articulo = '1001' and pc.clave_cliente = 'LAP150'

I have executed it in sqlserver and it works perfectly, but in firebird it does not execute I get the following error
can't format message 13:896 -- message file C:\AcuaData\System\firebird.msg not found.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, column 40.
from.

I hope you can help me please

[Mark Rotteveel]

Your query is syntactically invalid. Firebird's `UPDATE` doesn't support
joins. See the Language Reference[1].

Instead, you should use `MERGE`[2]:

merge into pa
using (
select *
from prices_cli_cli pc
cross join claves_articulos
where pc.clave_cliente = 'LAP150'
) src
on src.price_company_id=pa.price_company_id
and src.article_id=pa.article_id
when matched then update set pa.price = '300'

As an aside, using a string literal for something which is a price feels
off.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-dml-update
[2]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-dml-merge
--
Mark Rotteveel

[Mauricio Ramos]

thank you very much for your great support friend  Mark 

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/7b87df19-26c0-1e23-2043-56e86de2532b%40lawinegevaar.nl.