Update or Insert into from a select

[Luigi Siciliano]

Hello,

  in Firebird is incorrect do a UPDATE OR INSERT INTO from a SELECT.

I can't  do "INSERT INTO" due a key violation (ARTICOLO_ID +
FORNITORE_ID + DATA_PREZZO are a primary key) because in select I have
much duplicate rows :(

There is a workaround to do this:

UPDATE OR INSERT INTO Articoli_condizioni_for_date (ARTICOLO_ID,
FORNITORE_ID, DATA_PREZZO, PREZZO, SCONTO1, SCONTO2, SCONTO3, PREZZO_NETTO)
select
  dc.articolo_id,
  dt.fornitore_id,
  dt.data,
  iif((dc.sconto1 + dc.sconto2 + dc.sconto3) > 0.00, dc.prezzo, 0) as
prezzo,
  dc.sconto1,
  dc.sconto2,
  dc.sconto3,
  iif((dc.sconto1 + dc.sconto2 + dc.sconto3) > 0.00, 0, dc.prezzo) as
prezzo_netto
from
  doc_corpo dc,
  doc_testa dt,
  Articoli_condizioni_FORN acf
where
  dc.doc_testa_id = dt.id
  and ((dt.documento_id = 'DTA') or (dt.documento_id = 'FF'))
  AND acf.articolo_id = DC.ARTICOLO_ID
  AND acf.fornitore_id = dt.fornitore_id
  and acf.data_prezzo > dt.data

Thanks

--
Luigi Siciliano

[Svein Erling Tysvær]

I think you are looking for the MERGE statement, not UPDATE OR INSERT. From your email, I'm a bit uncertain whether you are saying

(a) the rows exists in Articoli_cindizioni_for_date, or

(b) the select itself can return multiple rows with the same values for the three PK fields.

If (a) then MERGE is your answer, if (b) you have to change your select statement to not be ambiguous, Firebird cannot randomly choose which of the rows to use for the update.

HTH,

Set

--
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/981ff200-f0fa-da70-54da-2c84bc2fe95a%40tiscalinet.it.

[Tomasz Tyrakowski]

On 12.02.2021 at 11:37, Svein Erling Tysvær wrote:
> I think you are looking for the MERGE statement, not UPDATE OR INSERT. From
> your email, I'm a bit uncertain whether you are saying
>
> (a) the rows exists in Articoli_cindizioni_for_date, or
> (b) the select itself can return multiple rows with the same values for the
> three PK fields.
>
> If (a) then MERGE is your answer, if (b) you have to change your select
> statement to not be ambiguous, Firebird cannot randomly choose which of the
> rows to use for the update.

If (b), maybe select distinct would suffice.

cheers
Tomasz

________________________________


Ta wiadomość zawiera poufne informacje przeznaczone tylko dla adresata. Jeżeli nie jesteście Państwo jej adresatem, bądź otrzymaliście ją przez pomyłkę, prosimy o powiadomienie o tym nadawcy oraz trwałe jej usunięcie.

[Luigi Siciliano]

Hello,

Il 12/02/2021 11:50, Tomasz Tyrakowski ha scritto:
> On 12.02.2021 at 11:37, Svein Erling Tysvær wrote:
>
> If (b), maybe select distinct would suffice.

select distinct partially solves some rows that are perfectly identical
but there are some rows partially identical because the three fields in
PK is the same but the others not, some rows are:

BSC1110/30,     292,     25.03.2016,     0.00,     0,     0, 0,     0.74

BSC1110/30,     292,     25.03.2016,     0.80,     9,     0, 0,     0.00

There's a way, to modify the query by adding "distinct" and to select
only the first 1 of the rows above?

Thanks

--
Luigi Siciliano
--------------------------

[Tomasz Tyrakowski]

On 12.02.2021 at 12:40, Luigi Siciliano wrote:
> select distinct partially solves some rows that are perfectly identical
> but there are some rows partially identical because the three fields in
> PK is the same but the others not, some rows are:
>
> BSC1110/30, 292, 25.03.2016, 0.00, 0, 0, 0, 0.74
>
> BSC1110/30, 292, 25.03.2016, 0.80, 9, 0, 0, 0.00
>
> There's a way, to modify the query by adding "distinct" and to select
> only the first 1 of the rows above?

No, distinct covers all selected fields. In the case you described
above, group by is a better solution:

select f1, f2, f3, min(f4), min(f5), min(f6)
from sometable
group by 1, 2, 3

You have to decide, which aggregate function (min, max) to use for a
particular field (when two or more rows are selected for the same key
value, which values of the other fields you want to insert: minimal,
maximal, a sum of them, etc.).
But honestly, I'd rethink this whole design. What good is this data if
you insert an arbitrary chosen record from among many with the same key
fields? The information value of the rest of the fields is dubious (you
could as well insert zeros or nulls for all non-key fields).

[Luigi Siciliano]

Hello,

Il 12/02/2021 12:53, Tomasz Tyrakowski ha scritto:

On 12.02.2021 at 12:40, Luigi Siciliano wrote:

No, distinct covers all selected fields. In the case you described
above, group by is a better solution:

select f1, f2, f3, min(f4), min(f5), min(f6)
from sometable
group by 1, 2, 3

Ok.

But honestly, I'd rethink this whole design. What good is this data if
you insert an arbitrary chosen record from among many with the same key
fields? The information value of the rest of the fields is dubious (you
could as well insert zeros or nulls for all non-key fields).

The query will be executed only once to populate the table whose data will then be examined by the user and possibly corrected. Subsequently the table will be updated, as needed, only with data selected by the user.

Thanks.

--

Luigi Siciliano
--------------------------

[Svein Erling Tysvær]

  Possibly using a CTE with windowing function like

with tmp( ARTICOLO_ID, FORNITORE_ID, DATA_PREZZO, PREZZO, SCONTO1, SCONTO2, SCONTO3, PREZZO_NETTO, row_num ) as
(select

   dc.articolo_id,
   dt.fornitore_id,
   dt.data,
   iif((dc.sconto1 + dc.sconto2 + dc.sconto3) > 0.00, dc.prezzo, 0) as prezzo,
   dc.sconto1,
   dc.sconto2,
   dc.sconto3,

   iif((dc.sconto1 + dc.sconto2 + dc.sconto3) > 0.00, 0, dc.prezzo) as prezzo_netto,
   row_number() OVER ( PARTITION BY dc.articolo_id, dt.fornitore_id, dt.data
                       ORDER BY iif((dc.sconto1 + dc.sconto2 + dc.sconto3) > 0.00, dc.prezzo, 0),
                                dc.sconto1, dc.sconto2, dc.sconto3,
                                iif((dc.sconto1 + dc.sconto2 + dc.sconto3) > 0.00, 0, dc.prezzo) )
from doc_corpo dc
join doc_testa dt on dc.doc_testa_id = dt.id
join Articoli_condizioni_FORN acf on acf.articolo_id = DC.ARTICOLO_ID and acf.fornitore_id = dt.fornitore_id
where ((dt.documento_id = 'DTA') or (dt.documento_id = 'FF'))
  and acf.data_prezzo > dt.data )
select ARTICOLO_ID, FORNITORE_ID, DATA_PREZZO, PREZZO, SCONTO1, SCONTO2, SCONTO3, PREZZO_NETTO
from tmp
where row_num = 1

Though I'm still on Fb 2.5 myself and haven't used anything like row_number() or PARTITION BY since I think you need Firebird 3 for them to be available.

HTH,

Set

--
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/dd605e22-d6b0-1d4c-baf5-0429517fb27d%40tiscalinet.it.

[Luigi Siciliano]

Hello,

Il 12/02/2021 15:02, Svein Erling Tysvær ha scritto:
> Though I'm still on Fb 2.5 myself and haven't used anything like
> row_number() or PARTITION BY since I think you need Firebird 3 for
> them to be available.

I'm on Fb 2.5 too.

Thanks.

--
Luigi Siciliano