Click here to Skip to main content
12,071,437 members (35,526 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: MySQL
I have a sql query
delimiter '$$'
DROP PROCEDURE IF Exists addcol $$
 
CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS ( (
	SELECT * FROM information_schema.COLUMNS
	WHERE COLUMN_NAME='newcol' AND TABLE_NAME='pp_tbl1' AND TABLE_SCHEMA='mydb'
	) )	THEN 
	ALTER TABLE `mydb`.`pp_tbl1` ADD COLUMN `newcol` bit  NOT NULL default 0;
END IF;
END$$
delimiter ';'
CALL addcol();
DROP PROCEDURE addcol;

previously i added a column 'newcol' but deleted it using Navicat gui and then ran this query
but it is giving me 0 rows effected
when i execute this query

SELECT * FROM information_schema.COLUMNS
	WHERE COLUMN_NAME='newcol' AND TABLE_NAME='pp_tbl1' AND TABLE_SCHEMA='mydb'

it is giving me result but i cannot find the column in my table

i am using mysql 5.1

also check the below query which is not working correct if possible

delimiter ';;'
create procedure foo ()
begin
    declare continue handler for 1060 begin end;
    alter table `pp_tbl1` add `newcol` bit  NOT NULL default 0;
end;;
call foo();;
Posted 26-Feb-13 23:37pm
Edited 27-Feb-13 2:00am
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Your Alter Table Query is Wrong.
Key word Column should not be there.

Correct query is as follows.

ALTER TABLE `mydb`.`pp_tbl1` ADD `newcol` bit  NOT NULL default 0;
  Permalink  
Comments
agha_ali22 27-Feb-13 5:42am
   
but issue is same as above

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160208.1 | Last Updated 27 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100