UPDATE mysql fields using a value from a SELECT statement with a condition from another table

Question

I have two tables that are related to each other.

One is user_registration and the other is user_group.

Inside the user_registration table, I have the fields:

• id
• id_user
• date_begin
• date_end

And user_group:

• id
• id_user
• id_group

I have just tried to learn simple join statements to select some records based on their group id like:

SELECT a.id_user, a.date_begin, a.date_end, b.id_group
FROM user_registration as a
INNER JOIN user_group as b
ON a.id_user=b.id_user
WHERE b.id_group = '14'

But right now, what I am trying to do is to set the date_end to be the same as the date_begin where in the id_group of the records will be the same.

---

I already got it. This did the trick although I am not sure this is the most efficient way as I am still learning MySQL.

UPDATE user_registration AS a
INNER JOIN user_group AS b ON a.id_user = b.id_user
SET a.date_end = a.date_begin
WHERE b.id_group = '14'

Answer 1

As mentioned on the edit above, this accomplished what I was trying to do.

UPDATE user_registration AS a
INNER JOIN user_group AS b ON a.id_user = b.id_user
SET a.date_end = a.date_begin
WHERE b.id_group = '14'

Assuming that 14 is a group id.

I am not that certain about its efficiency though. I still have to do more readings and learn more about it.