Left join to existing table

Question

I have a Table A in my data base which I want to alter by adding new columns from another table B. The problem I encounter is that the left join works perfectly if I create a new table or no table. For example, the following query works:

select * from TABLE_A left join TABLE_B on TABLE_A.ID=TABLE_B.ID

However, when I use alter, I get an error message. The following line produces an error message 1064 (SQL syntax error):

alter TABLE_A as select * from TABLE_A left join TABLE_B on TABLE_A.ID=TABLE_B.ID

or

alter TABLE_A from TABLE_A left join TABLE_B on TABLE_A.ID=TABLE_B.ID

How would be the correct syntax to add columns to an existing table using a left join?

Thanks a lot.

EDIT: To make things more clear: Table A has columns A1 and A2, and ID. I want to add Columns B1 and B2 from Table B by using the key ID (the column ID is also in B).

Answer 1

You can create a table based on another table and populate it using CREATE TABLE ... SELECT command. However, this does not work with ALTER TABLE command.

If you want to add a new column to a table and populate it from another table, then you need to do it in 2 steps:

1. Issue an ALTER TABLE ... ADD COLUMN ... command. If you want to add multiple columns, then you need to issue multiple ALTER TABLE commands.

2. Issue an UPDATE statement to update the values of the new fields. The update can select values from other tables.

Another approach is to create a 3rd table using the CREATE TABLE ... SELECT statement where you combine data from the 2 tables in the select with a join.