Forum > Databases
[solved] Firebird: Copy table-entry and change only one value
Nicole:
Firebird / IBX
I want to copy an entry of a table and change ONE value of it. This is harder, than I thought. At least for me.
The working select statement for this entry reads
--- Code: MySQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---select name_, FK_KONTRAKT, Entry_price, exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date, entry_Time, exit_date, exit_time, Status_, comm, stop, target, best, worst, PL, Ergebnis, Margin, zuKonto from tbtrades where id_trade=355
What whall be changed is the value "zuKonto"
Within a method I prepared these 2 lines:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- IBQueryGeneric.ParamByName('zuKonto').AsInteger:=kto_; IBQueryGeneric.ParamByName('id').AsInteger:=id_;
There may be 2 ways to solve it:
1)
copy it and change only the value zuKonto in the copy data-entry. The challenge for me is to return the new primary key, which is "id_trade"
2) OR I can use the select statement to fill in new values into it by "INSERT". Unfortunately I do not know how to do select and set at the same time.
Thank you for hints.
korba812:
You can use the "insert into select" statement along with the parameters:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---INSERT INTO tbtrades (id, name_, FK_KONTRAKT, Entry_price, exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date, entry_Time, exit_date, exit_time, Status_, comm, stop, target, best, worst, PL, Ergebnis, Margin, zuKonto)SELECT :id, name_, FK_KONTRAKT, Entry_price, exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date, entry_Time, exit_date, exit_time, Status_, comm, stop, target, best, worst, PL, Ergebnis, Margin, :zuKonto FROM tbtrades WHERE id_trade=355 Then set the parameters (id, zuKonto) and execute SQL.
Nicole:
Thank you for the answer.
This would work as overwrite the existing entry, but not to copy it into a new one.
The problem is the id_trade (primary key).
Instead of generating a new id_trade, I read the message "id_trade already exists".
To avoid the confusion by the typo id instead of id_trade I copy the statement again.
This is ok of the syntax, but works as "copy into the existing one" instead of "copy it and generate a new id for this copy"
--- Code: MySQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---INSERT INTO tbtrades (id_trade, name_, FK_KONTRAKT, Entry_price, exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date, entry_Time, exit_date, exit_time, Status_, comm, stop, target, best, worst, PL, Ergebnis, Margin, zuKonto)SELECT id_trade, name_, FK_KONTRAKT, Entry_price, exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date, entry_Time, exit_date, exit_time, Status_, comm, stop, target, best, worst, PL, Ergebnis, Margin, zuKonto FROM tbtrades WHERE id_trade=355
korba812:
In your example, you actually add a record with the same primary key (id_trade). You need to generate a unique key for the id_trade field and supply it as a parameter (as in my example) or omit this field if you have a trigger that generates the primary key value.
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- INSERT INTO tbtrades (id_trade, name_, FK_KONTRAKT, Entry_price, exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date, entry_Time, exit_date, exit_time, Status_, comm, stop, target, best, worst, PL, Ergebnis, Margin, zuKonto) SELECT /* param -> */ :id_trade, name_, FK_KONTRAKT, Entry_price, exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date, entry_Time, exit_date, exit_time, Status_, comm, stop, target, best, worst, PL, Ergebnis, Margin, /* param -> */ :zuKonto FROM tbtrades WHERE id_trade=355
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---IBQueryGeneric.ParamByName('zuKonto').AsInteger:=kto_;IBQueryGeneric.ParamByName('id_trade').AsInteger:= NewUniqueId;
rvk:
--- Quote from: Nicole on September 20, 2023, 05:00:33 pm ---Instead of generating a new id_trade, I read the message "id_trade already exists".
--- End quote ---
And if your id_trade is an autogenerated key (generated by a trigger and generator or sequence) you can just omit that field.
In that case the trigger will see the field is null (not provided) and generate a new value.
(you can still do "WHERE id_trade = xxx" even if you don't use the value for INSERT.)
Navigation
[0] Message Index
[#] Next page