[TriLUG] MySQL - Alter Table command

[Jim Wright]

Michael Rulison wrote:
> I am trying to catenate two columns from b onto c, making sure that the
> field 'name' is matched before each record is added with the following sql:
> 
> ALTER TABLE cust_extract AS c ADD COLUMN prev_billing_date,
> ADD COLUMN next_billing_date FROM billing AS b WHERE c.name = b.name
> 
> I get this error message:
> 
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'as c add column prev_billing_date
> FROM billing AS b
> WHERE c.name = b.name' at line 1
> 
> With thanks to those with sharper eyes than mine,
> 

I think with what you are trying to do, you need two statements...the 
first to add the columns...

ALTER TABLE cust_extract ADD prev_billing_date DATE NULL,
ADD next_billing_date DATE NULL;

The next to update the data to match the billing table...

UPDATE cust_extract
SET cust_extract.prev_billing_date = billing.prev_billing_date,
cust_extract.next_billing_date = billing.next_billing_date
FROM cust_extract INNER JOIN billing ON cust_extract.name = billing.name;

Or something like that...I think that will work with MySQL, I'm more 
familiar with the details of syntax in SQL Server.