SQL Syntax - Update

When I run the update

UPDATE jcost SET jc.JOB_ID = j.JOB_ID
WHERE j.JOB = jc.JOB;

I get the following error

Query : update jcost set jc.JOB_ID = j.JOB_ID where j.JOB = jc.JOB
Error Code : 1054
Unknown column 'j.JOB' in 'where clause'
hdcowboyazAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
It looks like you're trying to pull j.job_id from a different table (or row, maybe) because the table names (or aliases) for the columns you're using don't match, but there's no other table joined in your query.

Secondly, the "set jc.job_id" will only work if the table name matches the table being updated. It's jcost, which doesn't match. You can alias jcost as jc though, if you prefer, but the above issue would still exist.

If you can explain which tables and columns the data is in that you're wanting to deal with, then we can help fix the query.
0
hdcowboyazAuthor Commented:
The table jcost is where I want to insert JOB_ID values from the job table. Both tables have JOB. It's simply seeing what the JOB_ID is for a given JOB in the job table and inserting it in the jcost table.
0
Terry WoodsIT GuruCommented:
Thanks for the clear explanation; that's exactly what I need.

I'd write it like this:

update jcost 
join job on job.job = jcost.job
set jcost.job_id = job.job_id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.