[solved: 3 ways of] insert select from - within the SAME table
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2024-04-20 09:39

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
TBEINKOMMENselect((SELECT a.TYP, a.ISIN, a.WKZ, a.BEZEICHNUNG,
a.EINTRAGSDATUM,    a.EMITTENT, a.EMISSIONSDATUM, a.AGIO,
a.RENDITE_P_A, a.EMISSIONSPREIS,    a.LAND, a.BRANCHE,
a.EMISSIONSVOLUMEN, a.ZINSBERECHNUNG, a.BESONDERHEITEN,   
a.KUPONTEXT, a.NOMINALZINSSATZ, a.MODIFIED_DURATION, a.RATING,
a.WAEHRUNG,    a.ZUHANDELNAN, a.MATURITY, a.KLEINSTEEINHEIT,
a.ERSTERKUPON, a.LETZTERKUPON,    a.EMISSIONSKURS_IN_PROZENT,
a.TILGUNGSKURS_IN_PROZENT,    a.KAUFKURS_IN_PROZENT,
a.STEUER_IN_PROZENT, a.STEUER_ALS_BETRAG,    a.KUPONS_IM_JAHR,
a.KUPON_ALS_BETRAG, a.DURATION, a.BEZEICHNUNG_LANG,   
a.BETRAG_INVESTIERT, a.KURS, a.SPESEN, a.DEPOT, a.MARKETMAKER,   
a.KAUFPREIS_SUMME )    FROM TBEINKOMMEN a WHERE    a.ID_EINKOMMEN =
'10');

----------------------------------------------------------------------
Nicole · 2024-04-20 17:19

my workaround was this, I added a RETURNING

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
TBEINKOMMEN (TYP)       select TYP from tbeinkommen where id_einkommen
= 2RETURNING id_einkommen;

----------------------------------------------------------------------
egsuh · 2024-04-22 05:23

If you are copying all the fields then you don't have to use any 
brackets.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
TBEINKOMMEN SELECT * FROM TBEINKOMMEN WHERE ID_EINKOMMEN = '10'; 
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
TBEINKOMMEN     SELECT  NEXT VALUE FOR ID_Generator,              TYP,
ISIN, WKZ, BEZEICHNUNG, ... -- other fields    FROM TBEINKOMMEN  
 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 · 2024-04-22 07:41

Quote from: Nicole on April 20, 2024, 11:39:34 amI 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
TBEINKOMMENselect((SELECT a.TYP, a.ISIN, a.WKZ, a.BEZEICHNUNG,
a.EINTRAGSDATUM,    a.EMITTENT, a.EMISSIONSDATUM, a.AGIO,
a.RENDITE_P_A, a.EMISSIONSPREIS,    a.LAND, a.BRANCHE,
a.EMISSIONSVOLUMEN, a.ZINSBERECHNUNG, a.BESONDERHEITEN,   
a.KUPONTEXT, a.NOMINALZINSSATZ, a.MODIFIED_DURATION, a.RATING,
a.WAEHRUNG,    a.ZUHANDELNAN, a.MATURITY, a.KLEINSTEEINHEIT,
a.ERSTERKUPON, a.LETZTERKUPON,    a.EMISSIONSKURS_IN_PROZENT,
a.TILGUNGSKURS_IN_PROZENT,    a.KAUFKURS_IN_PROZENT,
a.STEUER_IN_PROZENT, a.STEUER_ALS_BETRAG,    a.KUPONS_IM_JAHR,
a.KUPON_ALS_BETRAG, a.DURATION, a.BEZEICHNUNG_LANG,   
a.BETRAG_INVESTIERT, a.KURS, a.SPESEN, a.DEPOT, a.MARKETMAKER,   
a.KAUFPREIS_SUMME )    FROM TBEINKOMMEN a WHERE    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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbeinkommen (Typ,Isin,WKZ, etc......)SELECT a.Typ, a.Isin etc... FROM
tbeinkommen WHERE id_einkommen='10';  -- Is this really Text?!?!?!

----------------------------------------------------------------------
rvk · 2024-04-22 07:55

Quote from: egsuh on April 22, 2024, 07:23:12 amBut 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
TBEINKOMMENSELECT NULL, a.TYP, a.ISIN, a.WKZ, a.BEZEICHNUNG,
a.EINTRAGSDATUM,    a.EMITTENT, a.EMISSIONSDATUM....(with or without
RETURNING depending on your needs)