Felipe Marin Cypriano escreveu:

> Hello,
>
> I'm trying to fill a global temporary table using the new update or
> insert statement, when I try to execute this sql I get an error:
>
> insert into TMP_RPT_NF_INVENTARIO(ID_PRODUTO, QT_ENTRADA)
> select
> I.ID_Produto, cast(SUM(coalesce(PCI.Quant_Metros, PCI.Quant_Kilos,
> I.Quant)) as decimal(12,4)) as QtEntrada
> -- sql ommited
> This is the error:
> Invalid token.
> Dynamic SQL Error.
> SQL error code = -104.
> Token unknown - line 2, column 1.
> Select.
>
> If I use just a insert statement with a select it works just fine. But I
> need the update or insert.
>
> What I'm doing wrong? Why don't work?
>

Replying what you asked on the tracker...

update or insert into t (pk, x, y) values (?, ?, ?);

was created to "substitute" conjunct usage of:
update t set x = ?, y = ? where pk = ?;
insert into t (pk, x, y) values (?, ?, ?);

for MERGE, it would be:

merge into t t1
using t t2
on (t1.pk = t2.pk)
when matched then
update set x = ?, y = ?
when not matched then
insert values (pk, ?, ?);

There is three problems with this:
1) It's very complicated for a simple use
2) It create duplicate parameters
3) It inserts and selects from the same table, so it's potentially
dangerous.

For other cases, it generally looks ok and is standard SQL command, so
use it. I don't think we should change UPDATE OR INSERT.


Adriano