Good evening, HashCode. Please tell me the SQL query with which you can update certain fields of the table, taking values ​​from the first child elements?

For example, there is such a table, tbUsers

| id | Login | Password | Name | Referer | email |

and users have a table with additional emails

| id | user_id | email |

A user may have several additional emails.

How can users set the email field equal to the value of the first additional email?

| id | Login | Password | Name | Referer | email |

  • | id | user_id | email | <-

  • | id | user_id | email |

  • | id | user_id | email |

| id | Login | Password | Name | Referer | email |

  • | id | user_id | email | <-

  • | id | user_id | email |

ZY The table itself of users and additional e-mails is not used anywhere, taken as an example.

Thank you so much!

  • I would add another boolean column with the name main to the table with "additional" e-mails. And already in the description of the application logic I took this main e-mail. This is more convenient than updates. - orkaan

2 answers 2

So it should work under different versions:

 update tbUsers u set email=(select email from tbEmails e where u.id = e.user_id order by id limit 1)

    What version of your DBMS? ROW_NUMBER() will help you if your version supports. Number the additional emails and pick up those with which 1 is assigned + linked UPDATE , if your version supports)