ADD column if not exists in mysql [duplicate]
This question already has an answer here:
add column to mysql table if it does not exist
15 answers
I keep getting an error when I run this on mysql5.7
What am I doing wrong. I basically just want to add a column if the column doesnt already exist
DROP PROCEDURE IF EXISTS ALIASCOLUMN;
DELIMITER //
CREATE PROCEDURE ALIASCOLUMN()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255);
END //
DELIMITER;
CALL ALIASCOLUMN();
DROP PROCEDURE ALIASCOLUMN;
mysql
marked as duplicate by Bill Karwin
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 13 '18 at 19:43
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
add column to mysql table if it does not exist
15 answers
I keep getting an error when I run this on mysql5.7
What am I doing wrong. I basically just want to add a column if the column doesnt already exist
DROP PROCEDURE IF EXISTS ALIASCOLUMN;
DELIMITER //
CREATE PROCEDURE ALIASCOLUMN()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255);
END //
DELIMITER;
CALL ALIASCOLUMN();
DROP PROCEDURE ALIASCOLUMN;
mysql
marked as duplicate by Bill Karwin
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 13 '18 at 19:43
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
What is the error this is returning to you?
– rsz
Nov 13 '18 at 19:32
add a comment |
This question already has an answer here:
add column to mysql table if it does not exist
15 answers
I keep getting an error when I run this on mysql5.7
What am I doing wrong. I basically just want to add a column if the column doesnt already exist
DROP PROCEDURE IF EXISTS ALIASCOLUMN;
DELIMITER //
CREATE PROCEDURE ALIASCOLUMN()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255);
END //
DELIMITER;
CALL ALIASCOLUMN();
DROP PROCEDURE ALIASCOLUMN;
mysql
This question already has an answer here:
add column to mysql table if it does not exist
15 answers
I keep getting an error when I run this on mysql5.7
What am I doing wrong. I basically just want to add a column if the column doesnt already exist
DROP PROCEDURE IF EXISTS ALIASCOLUMN;
DELIMITER //
CREATE PROCEDURE ALIASCOLUMN()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255);
END //
DELIMITER;
CALL ALIASCOLUMN();
DROP PROCEDURE ALIASCOLUMN;
This question already has an answer here:
add column to mysql table if it does not exist
15 answers
mysql
mysql
asked Nov 13 '18 at 19:04
lmoorelmoore
1
1
marked as duplicate by Bill Karwin
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 13 '18 at 19:43
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Bill Karwin
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 13 '18 at 19:43
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
What is the error this is returning to you?
– rsz
Nov 13 '18 at 19:32
add a comment |
What is the error this is returning to you?
– rsz
Nov 13 '18 at 19:32
What is the error this is returning to you?
– rsz
Nov 13 '18 at 19:32
What is the error this is returning to you?
– rsz
Nov 13 '18 at 19:32
add a comment |
2 Answers
2
active
oldest
votes
Use the following in a stored procedure:
IF NOT EXISTS( SELECT *
FROM 'tablename'
WHERE table_name = 'tablename'
AND table_schema = 'db_name'
AND column_name = 'columnname') THEN
ALTER TABLE `human_api_procedure` ADD `alias` varchar(255) ;
END IF;
Check this link. It may help you. :)
add a comment |
DDL statements within a procedure have to be executed as dynamic SQL. (I think that's true, I'd need to check the docs to verify.)
Also, identifiers (table names, column names) should not be enclosed in single quotes. Single quotes are delimiters for string literals.
I'd expect this statement to fail with a syntax error:
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255)
This
ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)
^ ^ ^ ^
or this
ALTER TABLE `human_api_procedure` ADD COLUMN `alias` varchar(255)
^ ^ ^ ^
would be valid syntax. We could also use double quotes around identifiers, if ANSI_QUOTES is included in sql_mode.
To execute an ALTER TABLE
statement in the context of a MySQL stored program, we could do something like this:
DELIMITER $$
CREATE PROCEDURE aliascolumn()
BEGIN
SET @sql = 'ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
MySQL permits routines to contain DDL statements, such as CREATE and DROP.
https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
The pattern demonstrated above, using the PREPARE/EXECUTE/DEALLOCATE would allow execution of for dynamic SQL, not just static SQL text.
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use the following in a stored procedure:
IF NOT EXISTS( SELECT *
FROM 'tablename'
WHERE table_name = 'tablename'
AND table_schema = 'db_name'
AND column_name = 'columnname') THEN
ALTER TABLE `human_api_procedure` ADD `alias` varchar(255) ;
END IF;
Check this link. It may help you. :)
add a comment |
Use the following in a stored procedure:
IF NOT EXISTS( SELECT *
FROM 'tablename'
WHERE table_name = 'tablename'
AND table_schema = 'db_name'
AND column_name = 'columnname') THEN
ALTER TABLE `human_api_procedure` ADD `alias` varchar(255) ;
END IF;
Check this link. It may help you. :)
add a comment |
Use the following in a stored procedure:
IF NOT EXISTS( SELECT *
FROM 'tablename'
WHERE table_name = 'tablename'
AND table_schema = 'db_name'
AND column_name = 'columnname') THEN
ALTER TABLE `human_api_procedure` ADD `alias` varchar(255) ;
END IF;
Check this link. It may help you. :)
Use the following in a stored procedure:
IF NOT EXISTS( SELECT *
FROM 'tablename'
WHERE table_name = 'tablename'
AND table_schema = 'db_name'
AND column_name = 'columnname') THEN
ALTER TABLE `human_api_procedure` ADD `alias` varchar(255) ;
END IF;
Check this link. It may help you. :)
edited Nov 13 '18 at 19:13
answered Nov 13 '18 at 19:07
Sachin ShahSachin Shah
1,5261415
1,5261415
add a comment |
add a comment |
DDL statements within a procedure have to be executed as dynamic SQL. (I think that's true, I'd need to check the docs to verify.)
Also, identifiers (table names, column names) should not be enclosed in single quotes. Single quotes are delimiters for string literals.
I'd expect this statement to fail with a syntax error:
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255)
This
ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)
^ ^ ^ ^
or this
ALTER TABLE `human_api_procedure` ADD COLUMN `alias` varchar(255)
^ ^ ^ ^
would be valid syntax. We could also use double quotes around identifiers, if ANSI_QUOTES is included in sql_mode.
To execute an ALTER TABLE
statement in the context of a MySQL stored program, we could do something like this:
DELIMITER $$
CREATE PROCEDURE aliascolumn()
BEGIN
SET @sql = 'ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
MySQL permits routines to contain DDL statements, such as CREATE and DROP.
https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
The pattern demonstrated above, using the PREPARE/EXECUTE/DEALLOCATE would allow execution of for dynamic SQL, not just static SQL text.
add a comment |
DDL statements within a procedure have to be executed as dynamic SQL. (I think that's true, I'd need to check the docs to verify.)
Also, identifiers (table names, column names) should not be enclosed in single quotes. Single quotes are delimiters for string literals.
I'd expect this statement to fail with a syntax error:
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255)
This
ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)
^ ^ ^ ^
or this
ALTER TABLE `human_api_procedure` ADD COLUMN `alias` varchar(255)
^ ^ ^ ^
would be valid syntax. We could also use double quotes around identifiers, if ANSI_QUOTES is included in sql_mode.
To execute an ALTER TABLE
statement in the context of a MySQL stored program, we could do something like this:
DELIMITER $$
CREATE PROCEDURE aliascolumn()
BEGIN
SET @sql = 'ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
MySQL permits routines to contain DDL statements, such as CREATE and DROP.
https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
The pattern demonstrated above, using the PREPARE/EXECUTE/DEALLOCATE would allow execution of for dynamic SQL, not just static SQL text.
add a comment |
DDL statements within a procedure have to be executed as dynamic SQL. (I think that's true, I'd need to check the docs to verify.)
Also, identifiers (table names, column names) should not be enclosed in single quotes. Single quotes are delimiters for string literals.
I'd expect this statement to fail with a syntax error:
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255)
This
ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)
^ ^ ^ ^
or this
ALTER TABLE `human_api_procedure` ADD COLUMN `alias` varchar(255)
^ ^ ^ ^
would be valid syntax. We could also use double quotes around identifiers, if ANSI_QUOTES is included in sql_mode.
To execute an ALTER TABLE
statement in the context of a MySQL stored program, we could do something like this:
DELIMITER $$
CREATE PROCEDURE aliascolumn()
BEGIN
SET @sql = 'ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
MySQL permits routines to contain DDL statements, such as CREATE and DROP.
https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
The pattern demonstrated above, using the PREPARE/EXECUTE/DEALLOCATE would allow execution of for dynamic SQL, not just static SQL text.
DDL statements within a procedure have to be executed as dynamic SQL. (I think that's true, I'd need to check the docs to verify.)
Also, identifiers (table names, column names) should not be enclosed in single quotes. Single quotes are delimiters for string literals.
I'd expect this statement to fail with a syntax error:
ALTER TABLE 'human_api_procedure' ADD COLUMN 'alias' varchar(255)
This
ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)
^ ^ ^ ^
or this
ALTER TABLE `human_api_procedure` ADD COLUMN `alias` varchar(255)
^ ^ ^ ^
would be valid syntax. We could also use double quotes around identifiers, if ANSI_QUOTES is included in sql_mode.
To execute an ALTER TABLE
statement in the context of a MySQL stored program, we could do something like this:
DELIMITER $$
CREATE PROCEDURE aliascolumn()
BEGIN
SET @sql = 'ALTER TABLE human_api_procedure ADD COLUMN alias varchar(255)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
MySQL permits routines to contain DDL statements, such as CREATE and DROP.
https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
The pattern demonstrated above, using the PREPARE/EXECUTE/DEALLOCATE would allow execution of for dynamic SQL, not just static SQL text.
edited Nov 13 '18 at 19:47
answered Nov 13 '18 at 19:41
spencer7593spencer7593
84.5k107994
84.5k107994
add a comment |
add a comment |
What is the error this is returning to you?
– rsz
Nov 13 '18 at 19:32