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.

How are we doing with your first question? When you ask for mysql support, you will always find that better, faster, clearer solutions will be delivered if you provide a sufficient amount of database details to recreate the process. — mickmackusa
– mickmackusa ♦, Commented Nov 13, 2018 at 21:01
The truth is, we don't even know if you should be using INSERT, UPDATE, REPLACE INTO, or INSERT ... ON DUPLICATE(). If you can explain with a bit of context we may be able to suggest a better query. — mickmackusa
– mickmackusa ♦, Commented Nov 13, 2018 at 21:56

Sharky · Accepted Answer · 2018-11-13 21:08:35Z

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] ;

Stack Exchange Network

MySQL update select one table from another

1 Answer 1

Hot Network Questions

MySQL update select one table from another

1 Answer 1

Related

Hot Network Questions