Selecting 1 row(rand) and updating it (Firebird+Delphi)

i think this should work:

spelare.sql.Clear;
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
 
spelare.sql.add('and NOT S.klubb = :param1  ORDER BY IIF(S.Nation = :param2, .2, .8) * RAND();');
spelare.sql.Params[0].AsString := QuotedStr( intklubb.caption );
spelare.sql.Params[1].AsString := QuotedStr( intland.caption );
 
Spelare.Open;
Spelare.First;

Select allOpen in new window

Thanks for your reply.

Is there something I have to do to make "Params[0]" etc work? As of now, I get "undeclared identifier: 'Params'".

oh ... i assumed (wrong) spelare to be a TQuery or a TQuery descendant and beside that I think i`ve misread you question so ignore my previous post.

now is that spelare object will be a TQuery then you can attach a TUpdateSQL to modify the content of the TQuery and only affect the table that you need.

Ah, alright. Never used TUpdateSQL (or since I'm using Zeos/Firebird I assume its ZUpdateSQL).

But not sure how to use it, tried the code below and other solutions but it's not working.

Spelare.UpdateObject := ZUpdateSQL1;
 
if intrykte.caption = '95' then
if inttyp.caption = 'F' then
begin
spelare.sql.Clear;
 
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');'
 
Spelare.Open;
Spelare.First;
 
ZUpdateSQL1.ModifySQL.Add('update spelare set klubbint = ''' + intklubb.caption+'''');

Select allOpen in new window

add this lines at the end

//...
 
//ZUpdateSQL1.ModifySQL.Add('update spelare set klubbint = ''' + intklubb.caption+'''');
 
ZUpdateSQL1.ExecSQL(ukModify);
Spelare.ApplyUpdates;
Spelare.Refresh;

Select allOpen in new window

That code will unfortunately also give me "Undeclared identifier" on ExecSQL. Tried changing "ZUpdateSQL1.ExecSQL(ukModify); to "Spelare.ExecSQL", but that would only give me "Cannot perform this operation on a closed dataset".

Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');
 
Spelare.Open;
Spelare.First;
 
ZUpdateSQL1.ModifySQL.Add('update spelare set klubbint = ''' + intklubb.caption+'''');
ZUpdateSQL1.ExecSQL(ukModify);
Spelare.ApplyUpdates;
Spelare.Refresh;

Select allOpen in new window

"after that result is picked the field "Klubbint" needs to be updated to intklubb.caption"
Are you trying to do this:
intklubb.caption := Spelare.Fieldbyname('Klubbint').AsString;
or are you trying to do this:
//Query Spelare has already been populated with a result set
Spelare.First;
While Spelare.eof = false do
begin
  Spelare.Edit;
  Spelare.Fieldbyname('Klubbint').value := strtoint(intklubb.caption);
  Spelare.Post;
  Spelare.Next;
end;
Or is Klubbint not part of your original query and you have to update it using a separate Query? In that case you will need an ID to refer to the records you want. You SQL would say something like UPDATE MyTable SET MyTable.Klubbint = ' + intklubb.caption + ' WHERE RecordID = 123

Query has already been populated with a result set (1 record). However, even if I do add that code nothing seems to happen.. the Klubbint field is still empty (even after a refresh).

Intklubb.caption is a string, but changing "value" into "asstring" etc made no difference. I do get an error when using "strtoint" and value, but when correcting this it just seems to skip by the code.

spelare.sql.Clear;
 
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');
 
Spelare.Open;
Spelare.First;
 
While Spelare.eof = false do
begin
 Spelare.Edit;
 Spelare.Fieldbyname('Klubbint').asstring := intklubb.caption;
 Spelare.Post;
 Spelare.Next;
end;
 
if not Spelare.IsEmpty then
begin
 
main_nyheter.lines.insert(3, '[INTRESSE] '+intklubb.caption+' är intresserade av '+Spelare['FNAMN']+' '+Spelare['ENAMN']+' i '+Spelare['Klubb']+'.');
 
main_nyheter.lines.insert(4, Spelare['FNamn']+' har den här säsongen spelat '+inttostr(Spelare['Matcher'])+' matcher och producerat '+inttostr(Spelare['Mal'])+' mål, '+inttostr(Spelare['Ass'])+' ass (totalt '+inttostr(Spelare['Poang'])+' poäng).');
 
main_nyheter.lines.Insert(5,'');
 
end;

Select allOpen in new window

"However, even if I do add that code nothing seems to happen.. the Klubbint field is still empty (even after a refresh)."
That's strange... Perhaps it is being updated in the database but your dataset isn't refereshing the data properly. You really should have Primary Keys and IDs in your tables so that you can update records that way in some cases. Also in some cases Query's can return read-only resultsets which would definitely prevent you from editing records. I don't think that's the case here because you would get an error.

Found that one of the problems I had was that I had ZUpdateSQL.

But now that I have removed that line and tried your solution, I get the message "Cannot update a complex query with more then one table".

//Using

if intrykte.caption = '95' then
if inttyp.caption = 'F' then
begin
spelare.sql.Clear;
 
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');
 
Spelare.Open;
Spelare.First;
 
While Spelare.eof = false do
begin
 Spelare.Edit;
 Spelare.Fieldbyname('Klubbint').asstring := intklubb.caption;
 Spelare.Post;
 Spelare.Next;
 end;

Select allOpen in new window

i`ve managed to do exactly what you want with a TQuery and a TUpdateSQL. I don`t have Zeos installed so i can`t help you the exact syntax ... look in the help of ZUpdateSQL to find out the proper way to call it.

I know.. I have pretty much the same query for other things in my code, and they are working.

Can't get by this error though - and when I do, well it doesnt update the table :/ Any ideas?

Posted all code from that button:

var i,bajs,x: integer;
begin
Randomize;
 
bajs := Random(10);
antalskadadespelare.caption := inttostr(bajs);
 
for x := 1 to (strtoint(antalskadadespelare.caption)) do begin
 
i := Random(2);
if i = 0 then
inttyp.caption := 'F'
else if i = 1 then
inttyp.caption := 'T';
 
Lag.close;
Lag.sql.Clear;
Lag.sql.add('select First 1 * from Lag where Rykte = ''95'' and NOT Division = ''AHL'' order by rand()');
Lag.Open;
Lag.First;
intklubb.caption := Lag['Klubb'];
intrykte.caption := Lag['Rykte'];
intland.caption := Lag['Nation'];
 
if intrykte.caption = '95' then
if inttyp.caption = 'F' then
begin
 
spelare.close;
spelare.sql.Clear;
 
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');
 
Spelare.Open;
Spelare.First;
 
While Spelare.eof = false do
begin
 Spelare.Edit;
 Spelare.Fieldbyname('Klubbint').asstring := intklubb.caption;
 Spelare.Post;
 Spelare.Next;
 end;
end;
end;

Select allOpen in new window

If you really can't create a primary key, then the WHERE part of your UPDATE statement will just have to be really long, listing all of your fields, something like this:
UPDATE myTable SET MyTable.KlubbInt = " + intklubb.caption
+ ' WHERE MyTable.Field1 = ' + Spelare.Fieldbyname('Field1').Asstring
+ ' and WHERE MyTable.Field2 = ' + Spelare.Fieldbyname('Field2').Asstring
+ ' and WHERE MyTable.Field3 = ' + Spelare.Fieldbyname('Field3').Asstring
+ ' and WHERE MyTable.Field4 = ' + Spelare.Fieldbyname('Field4').Asstring
+ ' and WHERE MyTable.Field5 = ' + Spelare.Fieldbyname('Field5').Asstring
+ ' and WHERE MyTable.Field6 = ' + Spelare.Fieldbyname('Field6').Asstring
etc etc etc

By the way my UPDATE SQL might not be 100% but at least you get the idea.
As long as the combination of fields in your WHERE statement will make sure that the correct record is updated then it will work. It is not best practise at all. This is the reason why they teach you to create an unique ID for every record called the Primary Key. You might have 2 players with the same name, so you need a way to identify each record by itself.
I hope some of this helps

Thanks for your effort and help.

My table doesn't have any Primary key / ID, but I am trying to create one using Firebird Maestro. I have had problems with 2 players having the same name, so I feel that I really do need some kind of unique ID/Key.

Tried creating it using triggers/generator and the code below(created by FB Maestro and according to http://www.firebirdsql.org/manual/generatorguide-rowids.html it should work), but I get the error:

"SQL Error:  unsuccessful metadata update DEFINE GENERATOR failed attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_11". Error Code: -607. This operation is not defined for system tables. The SQL: /* Autoincrement for field (ID) */
CREATE GENERATOR GEN_SPELARE_ID;"

/* Autoincrement for field (ID) */
CREATE GENERATOR GEN_SPELARE_ID;
 
SET TERM ^ ;
 
CREATE TRIGGER SPELARE_BI FOR SPELARE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
  NEW.ID = GEN_ID(GEN_SPELARE_ID,1);
END^
 
SET TERM ; ^

Select allOpen in new window

Or do I get this message because I already have lots of records (since its "active before Insert" and therefore it will not change my current records?)

Managed to set an ID for each record now using a generator and "update Spelare set ID=gen_id(Test, 1)"

So each record has an unique ID now, what would you recommend for updating it all now? Using the previously mentioned querys. Never used ID's before.

to sort out the id run:
update spelare set id = NULL;
this will for the trigger to be applied and each record to have an id
 

I have finally got it all working.

This is probably not the correct or most efficient way to use ID's though? At least its working, but if you have any other solution I'd be more than happy to learn it.

//Updating procedure
 
{Previous query's}
 
id.caption := Spelare['ID'];
end;
 
with Spelare do
      begin
      Close;
      SQL.Clear;
      SQL.Add('Update Spelare Set klubbint = '''+intklubb.caption+''' where ID = '''+id.caption+'''');
      ExecSQL;
      end;
end;

Select allOpen in new window

Well your code seems right, with some observations:
1) Your "with Spelare do" statement does not cycle through all its records - it will only update the Spelare table once, and only for whatever record it is currently on. If you want it to do all the records showing in the resultset then you will need to say FIRST, WHILE EOF = FALSE and NEXT etc.
2) I'm not sure why you are storing your parameters in TLabels or whatever. Normally you would just say:
      SQL.Add('Update Spelare Set klubbint = ''' + MyDataSet.Fieldbyname('klubbint').asstring + '' where ID = ''' + MyDataSet.Fieldbyname('ID').asstring  + ''';