• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

SQL Syntax - Update

When I run the update


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'
  • 2
1 Solution
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.
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.
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now