0

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 Nov 13 '18 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 Nov 13 '18 at 21:56
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.