There is a table to which an empty column is added. There is another table that is connected by id with the original one. The second table has the et.verification_period column — its values ​​must be copied to the et_attestation_period column of the first table. However, the developer swears that the subquery returns more than one row:

 update equipment set et_attestation_period = (select et.verification_period from metrology.equipment_type et join metrology.equipment e on et.id = e.equipment_type_id)

Please help with writing the right select for such an operation.

  • one
    Good day. First you need to decide what you want to get. Most likely, et.id in the main table is unique, and in the second table a one-to-many bundle goes to this key. Those. in the second table there may be several entries related to et.id Decide on what logic you want to transfer these several records? If one is the same, add TOP 1 to the subquery. - Deft
  • @Deft, in the equipment e table there is a column with unique e.id values. It corresponds to the column e.equipment_type_id - which contains the values ​​from the table equipment_type et. These tables are jointed by e.equipment_type_id = et.id. Each et.id value corresponds to a et.verification_period value. I need to assign this value to each record in the equipment table (column et_attestation period). Here is the whole logic. - n0n4m3

1 answer 1

 update equipment set et_attestation_period = (select verification_period from equipment_type where equipment_type.id = equipment.equipment_type_id )
  • Equipment - a table for 200k records, but the query is at least satisfied.) Thank you! - n0n4m3
  • Equipment - table for 200k records and what's the difference - how much? however, an equipment(equipment_type_id) index equipment(equipment_type_id) would be useful. And the equipment_type(id) index is probably already there. - Akina
  • and it would be better to use MERGE all the same - Ravshan Abdulaev