Hello i am having problems creating an update query, I have two tables both have an Title column and one table has an ID column. I want to take the ID from the table that has it when the titles of both columns match one of the titles against any of the others for example :

ID    Title          |  Title
1      Book1         |   Book3    ID 3
2      Book2         |   Book2    ID 2
3      Book3         |   Book4    ID 4
4      Book4         |   Book3    ID would be 3

So when one of the titles in the right table matches any of the ones on the left it takes the left tables ID and updates my 3rd Empty table's ID list and inserts that ID into it.

UPDATE Table3.newone p, Table1 a, Table2 b
ON b.Title = a.Title
SET p.ID = a.ID
WHERE a.Title = b.Title

I tried this, but it doesent quite work, i always get an error about: Unknown column 'a.Title' in 'on clause'
Even though all the tables have a Title column and have data in it and i have used the Title column in other queries without problems. So any help solving this task would be appreciated :)

5 Years
Discussion Span
Last Post by trishtren

inner join not need, just make ur where proper

UPDATE Table3.newone p, Table1 a, Table2 b
SET p.ID = a.ID
WHERE a.Title = b.Title
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.