I have two tables:
connections
id | publisherId | authorId
and
books
id | connectionId | title
I want to merge these tables to get only one table:
books
id| publisherId | authorId | title
How can I do this with only one SQL query?
-
You cannot perform both DML and DDL operations in one query. So you need to split it to: a) modify table b) modify datazerkms– zerkms2012-05-10 02:12:58 +00:00Commented May 10, 2012 at 2:12
-
Actually he wants to do four things, CREATE a newtable, populate it with the INNER JOIN as suggested by joe, then drop the old books table and renamed the newtable to books.Edwin Bautista– Edwin Bautista2012-05-10 02:18:30 +00:00Commented May 10, 2012 at 2:18
Add a comment
|
2 Answers
CREATE TABLE newtable
SELECT b.id, c.publisherId, c.authorID, b.title
FROM books b
INNER JOIN connections c
on c.id = b.connectionId
Untested, but this should do it. I assume you want the ID from the books table, otherwise you need c.id instead of b.id.
Then you can drop the old tables and rename this to whatever you want.
Comments
CREATE TABLE connections_books
SELECT books.id as id,
connections.publisherId as publisherId,
connections.authorId as authorId,
books.title as title
FROM books join connections on books.connectionId = connections.id;
Test the query first using just the select part:
SELECT books.id as id,
connections.publisherId as publisherId,
connections.authorId as authorId,
books.title as title
FROM books join connections on books.connectionId = connections.id;
Once this gives you what you want, go ahead and create the table.
It's important to make sure the column names in the new table are correct. Use the select statement testing to ensure your column names are what you want. If you want to change the column names, change the as ... names for each column selected.
