TrianglePHP Message Board › Alter table syntax, please
|A former member||
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,
|A former member||
I'm not 100% clear on what you are trying to do, but I do know that you are going about it incorrectly. :-) The ALTER TABLE statement is used to make changes to the table structure (like add a column), but it does not change the data that is in the table. It looks like you are trying to do both steps in one.
If you want to add a column to the table, you need something like this:
ALTER TABLE cust_extract ADD COLUMN prev_billing_date DATE;
This will add one column called 'prev_billing_date' that will store DATE data. You can change DATE to TIMESTAMP, or DATETIME, or which ever data type you need.
After your columns are added, you then need to insert data into the new columns. This can be done using the UPDATE statement. For example:
UPDATE cust_extract SET prev_billing_date = '2006-11-26';
Though, in your case, it looks like you want to insert data from another table where the name is the same. So maybe this is what you want:
UPDATE cust_extract, billing
SET cust_extract.prev_billing_date = billing.prev_billing_date
WHERE cust_extract.name = billing.name;
The proper syntax for both the ALTER TABLE and UPDATE statements can be found here: http://dev.mysql.com/...
I hope this helps you some, or at least gets you on the right track.