[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  [Select][+][-]

1. select
2.  
3.  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

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.



 

[korba812]

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]

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]

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]

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]

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);
5. end

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

[rvk]

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]

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]

Thank you for all answers.

The version with NULL works now!
Love it.