INSERT INTO tbl_1 with data from tbl_2 with a WHERE clause

I need to copy some data from one table to another but I need to use a WHERE clause in order to have it all work properly.

tbl_1 has classID开发者_如何学C vendorID category_id (EDIT: category_id is currently empty, this is what I'm trying to copy from the other table...) and departmentID

tbl_2 has class_code department_id and category_id

I want to grab the category_id in tbl_2 and put it into tbl_1 where the tbl_1.classID = tbl_2.class_code AND tbl_1.departmentID = tbl_2.department_id

I tried using:

INSERT INTO tbl_1
SELECT tbl_2.gateway_id
FROM tbl_2
WHERE tbl_1.classID = tbl_2.class_code 
AND tbl_1.departmentID = tbl_2.department_id

But, no luck. I get an error 'Unknown column tbl_1.classID in WHERE clause'

Should I be using UPDATE or something like that?

Thanks for any help.

---

I think you are looking for a UPDATE to update existing rows in tbl_1 with the correct category_id taken from tbl_2, not a INSERT which will add rows to tbl_1

UPDATE tbl_1 JOIN tbl_2 ON 
    tbl_1.classID = tbl_2.class_code 
    AND tbl_1.departmentID = tbl_2.department_id
SET tbl_1.category_id = tbl_2.category_id

---

Your insert statement should specify which columns the data is being inserted into, and the select needs to specify that you're also selecting from tbl_1. See the INSERT...SELECT page in the MySQL manual.

INSERT INTO tbl_1 (classID, departmentID)
    SELECT tbl_1.classID, tbl_1.class_code, tbl_2.gateway_id
    FROM tbl_1
    RIGHT JOIN tbl_2 ON tbl_2.class_code = tbl_1.classID
    WHERE tbl_1.departmentID = tbl_2.department_id

Obviously, you'll want to fix this to work with your specific example.

---

May be this helps you:

INSERT INTO tbl_1 (classID, vendorID, category_id, departmentID)
values
(
SELECT tbl_2.class_code, tbl_2.gateway_id, tbl_2.category_id, tbl_2.department_id
FROM tbl_2
)