Hi I am looking to create a table with date as column name.
I am using this code to Add column to the table:
DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
WHILE dateStart <= dateEnd DO
ALTER TABLE dates
ADD dateStart VARCHAR(30);
SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
END;
|
DELIMITER ;
CALL filldates('2017-01-01','2017-12-31');
But it is showing the error dateStart as duplicate content, because it creates a column 'dateStart' instead of date. How can I use dateStart as a varible.
When I use "INSERT INTO tablename (_date) VALUES (dateStart);" instead of ALTER TABLE statement, it doesn't show any error and it is inserting the dates to database, but as rows in '_date' column. I want the dates to be added as column name.
How can use dateStart as a variable
The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows. ALTER TABLE table_name ADD column_name datatype; The basic syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as follows.
Learn MySQL from scratch for Data Science and Analytics To add a column in a table in MySQL, we can use ALTER command with add column command. First, let us create a table with columns Id and Name. After that, we will add column name Age and Address with the help of ALTER command.
You can add multiple columns to an SQL table using the ALTER TABLE syntax. To do so, specify multiple columns to add after the ADD keyword. Separate each column you want to add using a comma.
ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table. ALTER TABLE – ADD. ADD is used to add columns into the existing table.
The short answer is that you cannot use variables as database, table, or column names in MySQL.
The only thing that you can do is to concatenate the sql statement as string and execute it as a prepared statement.
SET @s=CONCAT('ALTER TABLE dates ADD COLUMN `',dateStart,'` VARCHAR(30)');
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
However, adding field names dynamically in large quantities to a table usually indicates a bad design (unless your are preparing materialised pivot tables for reporting purposes out of data that cannot be changed).
This going to be Add Column of your Table but this is not a good idea.
set @datestart = "2017-01-03";
set @sql = concat('Alter table tbl_1 Add Column `' ,@datestart, '`Varchar(30)');
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
If you wanted a loop then use Cursor
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With