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 :)

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

worked like a charm thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.