Member Avatar for trishtren

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
INNER JOIN Table1
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 :)

  • 2 Contributors
  • 2 Replies
  • 197 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by trishtren
Member Avatar for urtrivedi

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
Member Avatar for trishtren

worked like a charm thanks

Be a part of the DaniWeb community

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