Recent

Author Topic: [solved] Firebird: Copy table-entry and change only one value  (Read 1161 times)

Nicole

  • Hero Member
  • *****
  • Posts: 1009
[solved] Firebird: Copy table-entry and change only one value
« on: September 20, 2023, 11:46:48 am »
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  [Select][+][-]
  1.  
  2.  name_, FK_KONTRAKT,
  3.   Entry_price,
  4.   exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ,
  5.   Position_, entry_date, entry_Time, exit_date, exit_time,
  6.   Status_, comm, stop, target, best, worst, PL,
  7.   Ergebnis, Margin, zuKonto
  8.  
  9.  from tbtrades
  10.   where id_trade=355

What whall be changed is the value "zuKonto"

Within a method I prepared these 2 lines:
Code: Pascal  [Select][+][-]
  1.   IBQueryGeneric.ParamByName('zuKonto').AsInteger:=kto_;
  2.   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.



 
« Last Edit: September 20, 2023, 06:33:10 pm by Nicole »

korba812

  • Sr. Member
  • ****
  • Posts: 442
Re: Firebird: Copy table-entry and change only one value
« Reply #1 on: September 20, 2023, 12:32:46 pm »
You can use the "insert into select" statement along with the parameters:

Code: SQL  [Select][+][-]
  1. 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)
  2. SELECT    
  3.      :id, name_, FK_KONTRAKT,
  4.       Entry_price,
  5.       exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ,
  6.       Position_, entry_date, entry_Time, exit_date, exit_time,
  7.       Status_, comm, stop, target, best, worst, PL,
  8.       Ergebnis, Margin, :zuKonto
  9.      
  10.      FROM tbtrades
  11.       WHERE id_trade=355
  12.  
Then set the parameters (id, zuKonto) and execute SQL.

Nicole

  • Hero Member
  • *****
  • Posts: 1009
Re: Firebird: Copy table-entry and change only one value
« Reply #2 on: September 20, 2023, 05:00:33 pm »
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  [Select][+][-]
  1. 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)
  2. SELECT    
  3.      id_trade, name_, FK_KONTRAKT,
  4.       Entry_price,
  5.       exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ,
  6.       Position_, entry_date, entry_Time, exit_date, exit_time,
  7.       Status_, comm, stop, target, best, worst, PL,
  8.       Ergebnis, Margin, zuKonto
  9.      
  10.      FROM tbtrades
  11.       WHERE id_trade=355

korba812

  • Sr. Member
  • ****
  • Posts: 442
Re: Firebird: Copy table-entry and change only one value
« Reply #3 on: September 20, 2023, 05:14:50 pm »
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  [Select][+][-]
  1.     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)
  2.     SELECT    
  3. /* param -> */ :id_trade, name_, FK_KONTRAKT,
  4.           Entry_price,
  5.           exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ,
  6.           Position_, entry_date, entry_Time, exit_date, exit_time,
  7.           Status_, comm, stop, target, best, worst, PL,
  8.           Ergebnis, Margin, /* param -> */ :zuKonto
  9.          
  10.          FROM tbtrades
  11.           WHERE id_trade=355

Code: Pascal  [Select][+][-]
  1. IBQueryGeneric.ParamByName('zuKonto').AsInteger:=kto_;
  2. IBQueryGeneric.ParamByName('id_trade').AsInteger:= NewUniqueId;
  3.  

rvk

  • Hero Member
  • *****
  • Posts: 6589
Re: Firebird: Copy table-entry and change only one value
« Reply #4 on: September 20, 2023, 05:31:17 pm »
Instead of generating a new id_trade, I read the message "id_trade already exists".
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.)

Nicole

  • Hero Member
  • *****
  • Posts: 1009
Re: Firebird: Copy table-entry and change only one value
« Reply #5 on: September 20, 2023, 05:32:39 pm »
thank you for your reply.

on
IBQueryGeneric.ParamByName('id_trade').AsInteger:= NewUniqueId;
I read:

"Identifier not found"

Within my FlameRobin (oh dear, it is years ago, I wrote this! I cannot remember any more, how it works)
if found a

before insert which reads
Code: PHP  [Select][+][-]
  1. as
  2. begin
  3.   if (new.id_trade is null) then
  4.     new.id_trade = gen_id(gen_tbtrades_id,1);

I better not touch it, I will destroy it and never get it back. %)


rvk

  • Hero Member
  • *****
  • Posts: 6589
Re: Firebird: Copy table-entry and change only one value
« Reply #6 on: September 20, 2023, 05:37:55 pm »
on
IBQueryGeneric.ParamByName('id_trade').AsInteger:= NewUniqueId;
I read:

"Identifier not found"
Show the complete code then.

But as I said before, you have a trigger for NULL, so you can omit the id_trade in the SELECT/INSERT.

You can also just pass NULL as SELECT value for id_trade.

Code: SQL  [Select][+][-]
  1. 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)
  2.     SELECT    
  3. /* constant -> */ NULL, FK_KONTRAKT,
  4.           Entry_price,
  5.           exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ,
  6.           Position_, entry_date, entry_Time, exit_date, exit_time,
  7.           Status_, comm, stop, target, best, worst, PL,
  8.           Ergebnis, Margin, /* param -> */ :zuKonto
  9.          
  10.          FROM tbtrades
  11.           WHERE id_trade=355

You see that you can just change one value in the SELECT, it doesn't need to be a field value, you can also pass a constant in the SELECT.

korba812

  • Sr. Member
  • ****
  • Posts: 442
Re: Firebird: Copy table-entry and change only one value
« Reply #7 on: September 20, 2023, 05:43:45 pm »
thank you for your reply.

on
IBQueryGeneric.ParamByName('id_trade').AsInteger:= NewUniqueId;
I read:

"Identifier not found"
This is pseudo code and not an actual function. I meant that you have to pass a new unique id as a parameter. You need to generate it beforehand (e.g. using gen_id)

Nicole

  • Hero Member
  • *****
  • Posts: 1009
Re: Firebird: Copy table-entry and change only one value
« Reply #8 on: September 20, 2023, 06:26:39 pm »
Thank you for all answers.

The version with NULL works now!
Love it.

 

TinyPortal © 2005-2018