I have a Persons table, which has a DefaultEamil column.

I also have an Emails table which has PersonId, Value, IsDefault columns. A person can not have multiple default emails. There is an index constraint on it.

For the sake of performance and easier filtering, I want to update the Persons table and set the DefaultEmail to the Value of the default email.

This is what I wrote:

update Persons
set DefaultEmail = 
    select Value
    from Emails
    where IsDefault = true
    and PersonId = Persons.Id

It executes but reports that only 0 rows are updated.

What could be wrong?

I'm using:

MySQL version: 11.1.2-MariaDB-1:11.1.2+maria~ubu2204 through PHP extension MySQLi


I also tried the following query, but again it reports 0 rows:

update Persons p
left join
    select PersonId, Value
    from Emails
    where IsDefault = true
) as e
on p.Id = e.PersonId
set p.DefaultEmail = e.Value
  • Not related to your specific question , but why there are a lot of not accepted answers on your previous questions ? As per the given question, please add some data example and expected result in text format. Would be much better if you reproduce your issue on db<>fiddle Commented Sep 30, 2023 at 13:01
  • @ErgestBasha simply because we did not get workable answers. Yet I marked them as accepted answers if that makes you happy. Commented Sep 30, 2023 at 13:13
  • @ErgestBasha, I'll try to create that. But I asked it since these queries seem to be totally OK. I just thought maybe I have not understood something fundamental about how to write queries. Commented Sep 30, 2023 at 13:14
  • 1
    I can't reproduce your issue. It works fine here. Maybe I'm missing something Commented Sep 30, 2023 at 13:30
  • How many rows does SELECT * FROM Emails WHERE IsDefault = true return? How about the query SELECT * FROM Persons p INNER JOIN Emails e on p.Id = e.PersonId?
    – J.D.
    Commented Sep 30, 2023 at 21:06


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.