MySQL: ALTER TABLE if column not exists
Use the following in a stored procedure:
IF NOT EXISTS( SELECT NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tablename'
AND table_schema = 'db_name'
AND column_name = 'columnname') THEN
ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';
END IF;
Add a column to a table, if it does not already exist
You can use a similar construct by using the sys.columns
table io sys.objects
.
IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID(N'[dbo].[Person]')
AND name = 'ColumnName'
)
Adding column if it does not exist
Another option that does not require creating a helper function (or an already complete data.frame) using tibble's add_column
:
library(tibble)
cols <- c(top_speed = NA_real_, nhj = NA_real_, mpg = NA_real_)
add_column(mtcars, !!!cols[setdiff(names(cols), names(mtcars))])
add column to mysql table if it does not exist
Note that INFORMATION_SCHEMA
isn't supported in MySQL prior to 5.0. Nor are stored procedures supported prior to 5.0, so if you need to support MySQL 4.1, this solution isn't good.
One solution used by frameworks that use database migrations is to record in your database a revision number for the schema. Just a table with a single column and single row, with an integer indicating which revision is current in effect. When you update the schema, increment the number.
Another solution would be to just try the ALTER TABLE ADD COLUMN
command. It should throw an error if the column already exists.
ERROR 1060 (42S21): Duplicate column name 'newcolumnname'
Catch the error and disregard it in your upgrade script.
Insert column if it doesn't exist
Try this:
IF (SELECT COUNT(*) FROM syscolumns WHERE name = 'HasAccess24_7'
AND OBJECT_NAME(object_id) = 'tSafeUnit') = 0
BEGIN
ALTER TABLE dbo.tSafeUnit ADD HasAccess24_7 tinyint not null default 0
END
When using a pandas dataframe, how do I add column if does not exist?
You check it like this:
if 'Met' not in df:
df['Met'] = df['freqC'] * df['coverage']
Related Topics
Install Udunits2 Package for R3.3
Get a List of the Data Sets in a Particular Package
R Create Reference Manual with R Cmd Check
Format Numbers to Significant Figures Nicely in R
Possible to Create Latex Multicolumns in Xtable
How to Change Positions of X and Y Axis in Ggplot2
Knitr Wont Compile PDF: "Error in Tools::File_Path_As_Absolute(Output_File)"
Grouping 2 Levels of a Factor in R
Catching an Error and Then Branching Logic
One Function to Detect Nan, Na, Inf, -Inf, etc.
Add Color to Boxplot - "Continuous Value Supplied to Discrete Scale" Error
Show Correlations as an Ordered List, Not as a Large Matrix
Mutate with Case_When and Contains