Recent

Author Topic: [solved: 3 ways of] insert select from - within the SAME table  (Read 835 times)

Nicole

  • Hero Member
  • *****
  • Posts: 983
I use Firebird 4 and want to do this:
- copy one existing dataset, no matter if with * or listed items
- make my generator make an new id within the same table
- copy the values into it

I tried it all, with all brackets settings.
The error message jumps just before the bracket and claims "invalid token".

How to make it work?
If possible I prefer select *, but there was a bug with it in a previous Firebird version (2.5.)

This version below does not work.
The idea was: select everything except the id = id_einkommen and make Firebird generate a new id with inserting the values of the old one, e.g. 10.

Code: MySQL  [Select][+][-]
  1. INSERT INTO TBEINKOMMEN
  2. ((
  3. SELECT a.TYP, a.ISIN, a.WKZ, a.BEZEICHNUNG, a.EINTRAGSDATUM,
  4.     a.EMITTENT, a.EMISSIONSDATUM, a.AGIO, a.RENDITE_P_A, a.EMISSIONSPREIS,
  5.     a.LAND, a.BRANCHE, a.EMISSIONSVOLUMEN, a.ZINSBERECHNUNG, a.BESONDERHEITEN,
  6.     a.KUPONTEXT, a.NOMINALZINSSATZ, a.MODIFIED_DURATION, a.RATING, a.WAEHRUNG,
  7.     a.ZUHANDELNAN, a.MATURITY, a.KLEINSTEEINHEIT, a.ERSTERKUPON, a.LETZTERKUPON,
  8.     a.EMISSIONSKURS_IN_PROZENT, a.TILGUNGSKURS_IN_PROZENT,
  9.     a.KAUFKURS_IN_PROZENT, a.STEUER_IN_PROZENT, a.STEUER_ALS_BETRAG,
  10.     a.KUPONS_IM_JAHR, a.KUPON_ALS_BETRAG, a.DURATION, a.BEZEICHNUNG_LANG,
  11.     a.BETRAG_INVESTIERT, a.KURS, a.SPESEN, a.DEPOT, a.MARKETMAKER,
  12.     a.KAUFPREIS_SUMME )
  13.     FROM TBEINKOMMEN a
  14.     a.ID_EINKOMMEN = '10');





« Last Edit: April 23, 2024, 06:10:42 pm by Nicole »

Nicole

  • Hero Member
  • *****
  • Posts: 983
Re: insert select from - within the SAME table
« Reply #1 on: April 20, 2024, 07:19:45 pm »
my workaround was this, I added a RETURNING

Code: MySQL  [Select][+][-]
  1. INSERT INTO TBEINKOMMEN (TYP)
  2.        select TYP from tbeinkommen where id_einkommen = 2
  3. RETURNING id_einkommen;

egsuh

  • Hero Member
  • *****
  • Posts: 1330
Re: insert select from - within the SAME table
« Reply #2 on: April 22, 2024, 07:23:12 am »
If you are copying all the fields then you don't have to use any  brackets.

Code: SQL  [Select][+][-]
  1. INSERT INTO TBEINKOMMEN SELECT * FROM TBEINKOMMEN WHERE ID_EINKOMMEN = '10';
  2.  

But if the ID must be different, you may use trigger -- before insert. Set new.ID_einkommen to new id there.

Or,

Code: SQL  [Select][+][-]
  1. INSERT INTO TBEINKOMMEN
  2.     SELECT  NEXT VALUE FOR ID_Generator,
  3.              TYP, ISIN, WKZ, BEZEICHNUNG, ... -- other fields
  4.    FROM TBEINKOMMEN
  5.    WHERE  ID_EINKOMMEN = '10');

assuming that ID_EINKOMMEN is the first field. This works in Firebird.  But your field ID_EinKommen is string type, then I have no idea how you would create new id. You may use stored procedure to create a new id.

Zvoni

  • Hero Member
  • *****
  • Posts: 2364
Re: insert select from - within the SAME table
« Reply #3 on: April 22, 2024, 09:41:00 am »
I use Firebird 4 and want to do this:
- copy one existing dataset, no matter if with * or listed items
- make my generator make an new id within the same table
- copy the values into it

I tried it all, with all brackets settings.
The error message jumps just before the bracket and claims "invalid token".

How to make it work?
If possible I prefer select *, but there was a bug with it in a previous Firebird version (2.5.)

This version below does not work.
The idea was: select everything except the id = id_einkommen and make Firebird generate a new id with inserting the values of the old one, e.g. 10.

Code: MySQL  [Select][+][-]
  1. INSERT INTO TBEINKOMMEN
  2. ((
  3. SELECT a.TYP, a.ISIN, a.WKZ, a.BEZEICHNUNG, a.EINTRAGSDATUM,
  4.     a.EMITTENT, a.EMISSIONSDATUM, a.AGIO, a.RENDITE_P_A, a.EMISSIONSPREIS,
  5.     a.LAND, a.BRANCHE, a.EMISSIONSVOLUMEN, a.ZINSBERECHNUNG, a.BESONDERHEITEN,
  6.     a.KUPONTEXT, a.NOMINALZINSSATZ, a.MODIFIED_DURATION, a.RATING, a.WAEHRUNG,
  7.     a.ZUHANDELNAN, a.MATURITY, a.KLEINSTEEINHEIT, a.ERSTERKUPON, a.LETZTERKUPON,
  8.     a.EMISSIONSKURS_IN_PROZENT, a.TILGUNGSKURS_IN_PROZENT,
  9.     a.KAUFKURS_IN_PROZENT, a.STEUER_IN_PROZENT, a.STEUER_ALS_BETRAG,
  10.     a.KUPONS_IM_JAHR, a.KUPON_ALS_BETRAG, a.DURATION, a.BEZEICHNUNG_LANG,
  11.     a.BETRAG_INVESTIERT, a.KURS, a.SPESEN, a.DEPOT, a.MARKETMAKER,
  12.     a.KAUFPREIS_SUMME )
  13.     FROM TBEINKOMMEN a
  14.     a.ID_EINKOMMEN = '10');
This wouldn't work in any way. (Besides the wrongly placed brackets)
You're ommitting the Field "id_einkommen" in your Select, so your INSERT tries to Insert Field TYP into id_einkommen (if id_einkommen is the first field).

Bottom Line: If you're ommiting fields from the Select you have to explicitly state the Columns in the INSERT, and those columns must be in the same order as the fields in the Select.
egsuh alluded to this in his example

Aircode
Code: SQL  [Select][+][-]
  1. INSERT INTO tbeinkommen (Typ,Isin,WKZ, etc......)
  2. SELECT a.Typ, a.Isin etc...
  3. FROM tbeinkommen
  4. WHERE id_einkommen='10';  -- Is this really Text?!?!?!
  5.  
« Last Edit: April 22, 2024, 09:43:37 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

rvk

  • Hero Member
  • *****
  • Posts: 6199
Re: insert select from - within the SAME table
« Reply #4 on: April 22, 2024, 09:55:04 am »
But if the ID must be different, you may use trigger -- before insert. Set new.ID_einkommen to new id there.
Normally you could just pass NULL for ID. The trigger should handle creating a new ID in that case. (No need to use "NEXT VALUE")

So something like this should work too:
Code: SQL  [Select][+][-]
  1. INSERT INTO TBEINKOMMEN
  2. SELECT NULL, a.TYP, a.ISIN, a.WKZ, a.BEZEICHNUNG, a.EINTRAGSDATUM,
  3.     a.EMITTENT, a.EMISSIONSDATUM....
(with or without RETURNING depending on your needs)

 

TinyPortal © 2005-2018