There are two tables:

  1. Animal owners : information about pet owners
  2. Animals : in the format " IDвладельца - IDживотного - приоритет животного "

It is necessary to add in the table.1 the Главное животное field, where the Главное животное IDживотного found in the table.2 by the highest priority is recorded.

The question of how to add a field is not worth it, the question of how to choose an IDживотного with max priority is also.

The question is in what form (procedure, function) and how to carry out a one-time filling of the field thus created for all owners? That is, tell me, please, analogue foreach ID in table1 ....

  • And why not just create a field and fill it with one request? - Denis Startsev
  • what is meant? in the sense, I understand how to fill the field for one record - it turns out a construction with if exists, etc., and how to do it for all records? - reAnn
  • I may be mistaken in the syntax, but something like this UPDATE 'Animal owners' tvlad JOIN (SELECT ID owner, animal, animal priority FROM Animals GROUP BY ID owner HAVING MAX (animal priority)) tpets SET tvlad.'Main animal '= tpets.'ID Animal' WHERE tvlad.IDowner = tpets.IDowner - Denis Startsev
  • Nothing is clear. Add examples or something. - Igor Borisenko

1 answer 1

As already said in the comments, a single request is enough for the task to complete, without any cycles. Somehow, for example:

 UPDATE [Владельцы животных] SET [Главное животное] = t.main FROM (SELECT [IDвладельца], MAX([приоритет животного]) AS main FROM [Животные] GROUP BY [IDвладельца] ) AS t WHERE t.[IDвладельца] = [Владельцы животных].[IDвладельца]