ADD column if not exists in mysql [duplicate]












0
















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;









share|improve this question













marked as duplicate by Bill Karwin mysql
Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

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
















0
















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;









share|improve this question













marked as duplicate by Bill Karwin mysql
Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

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














0












0








0









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;









share|improve this question















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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 13 '18 at 19:04









lmoorelmoore

1




1




marked as duplicate by Bill Karwin mysql
Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

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 mysql
Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

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



















  • 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












2 Answers
2






active

oldest

votes


















0














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. :)






share|improve this answer

































    0














    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.






    share|improve this answer
































      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      0














      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. :)






      share|improve this answer






























        0














        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. :)






        share|improve this answer




























          0












          0








          0







          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. :)






          share|improve this answer















          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. :)







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 13 '18 at 19:13

























          answered Nov 13 '18 at 19:07









          Sachin ShahSachin Shah

          1,5261415




          1,5261415

























              0














              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.






              share|improve this answer






























                0














                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.






                share|improve this answer




























                  0












                  0








                  0







                  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.






                  share|improve this answer















                  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.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 13 '18 at 19:47

























                  answered Nov 13 '18 at 19:41









                  spencer7593spencer7593

                  84.5k107994




                  84.5k107994















                      Popular posts from this blog

                      Bressuire

                      Vorschmack

                      Quarantine