MySQL UPDATE problem

Question

I know the following MySQL code is not correct can some help me fix this code to use both tables I'm trying to grab the id from learned_skills and skill_id from users_skills for AND skill_id = id

Here is the MySQL code.

SELECT learned_skills.*, users_skills.* 
UPDATE learned_skills
SET skill = '$skill', experience = '$experience', years = '$years'
WHERE user_id = '$user_id'
AND skill_id = id

Here is the error I get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE learned_skills SET skill = 'some_skill', experience = '1 - 2 years' at line 2

Answer 1

Have a look at UPDATE Syntax and see the first user comment for help.

Answer 2

You can perform UPDATE operations covering multiple by using the JOIN syntax, as in the following example:

UPDATE  learned_skills ls
JOIN    user_skills us ON (us.skill_id = ls.id)
SET     skill = '$skill',
        experience = '$experience', 
        years = '$years'
WHERE   us.user_id = '$user_id';

Answer 3

The problem is the select statement on the first line. Remove it and run it seperately and see if that works. Also, qualify both of your tables in a join the way Daniel Vassalo suggested. Otherwise it wont know where to get half the columns from