CODEWITHSUNDEEP
mysqlsql
latca
latca

Reputation: 133

An SQL query to update a table field from information from two other tables

I have three tables: category, old, and new.

Goal: to update book_id from old to new

tables category has a column: book_id

tables old and new both have columns: id and isbn

What I want to achieve:

Running MySQL.

Upvotes: 1

Views: 238

Answers (2)

Bogdan
Bogdan

Reputation: 1393

If you update the category this should do it:

update cat 
set book_id = new.id
from category cat
join old on cat.book_id = old.id
join new on old.isbn = new.isbn

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

MySQL syntax is a little different that SQL Server or Oracle...

update category cat
inner join old on cat.book_id = old.id
inner join new on old.isbn = new.isbn
set cat.book_id = new.id

Upvotes: 2

Related Questions