MySQL update select one table from another

Question

I'm struggling to get this MySQL query to work and hoping someone out there can help please.

I wish to "copy" one record from table traincomprofiler to course_dates where both tables share the column 'user_id'. Currently the following code works to 'select' the correct data for the table traincomprofiler:

SELECT t1.cb_area_code
FROM traincomprofiler as t1, traincomprofiler AS t2 
WHERE t1.cb_role = 'services'
AND t1.cb_service_name = t2.cb_service_name
AND t2.user_id = [user_id] ;

(Note: The [user_id] simply establishes the id of the currently logged in user for CB)

So, when I have tried to insert into the table course_dates using the following:

INSERT INTO course_dates
SET area_code =
(SELECT t1.cb_area_code
FROM traincomprofiler as t1, traincomprofiler AS t2 
WHERE t1.cb_role = 'services'
AND t1.cb_service_name = t2.cb_service_name
AND t2.user_id = [user_id] ) ;

It results in the following error:

Duplicate entry '0' for key 'user_id'

Then I try to UPDATE with:

UPDATE course_dates
SET area_code =
(SELECT t1.cb_area_code
FROM traincomprofiler as t1, traincomprofiler AS t2 
WHERE t1.cb_role = 'services'
AND t1.cb_service_name = t2.cb_service_name
AND t2.user_id = [user_id] ) ;

Results in every record in course_dates.area_code being populated with the result rather than just the correct user! This SQL query is being run through Community Builder Auto Action which is a great way for me to work with MySQL but this has me stumped. Can anyone help please?

Many thanks in advance for your help.

Answer 1

You are missing a WHERE clause for UPDATE/SET statement to indicate that a row with specific user ID must be updated. It's not to be mistaken with the clause inside SELECT statement.

UPDATE course_dates as cd
SET cd.area_code =
(SELECT t1.cb_area_code
FROM traincomprofiler as t1, traincomprofiler AS t2 
WHERE t1.cb_role = 'services'
AND t1.cb_service_name = t2.cb_service_name
AND t2.user_id = [user_id] ) WHERE cd.user_id = [user_id] ;

Also, I'm not sure what's the purpose of selecting a value of one table from two identical tables. So perhaps second table can be removed. This would be the result (haven't tested):

UPDATE course_dates as cd
SET cd.area_code =
(SELECT t1.cb_area_code
FROM traincomprofiler as t1 
WHERE t1.cb_role = 'services'
AND t1.user_id = [user_id] ) WHERE cd.user_id = [user_id] ;