Click here to Skip to main content
Rate this: bad
good
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 at 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
0 OriginalGriff 329
1 Maciej Los 290
2 Sergey Alexandrovich Kryukov 230
3 Shweta N Mishra 185
4 PIEBALDconsult 149
0 OriginalGriff 7,660
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,760


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 27 Feb 2013
Copyright © CodeProject, 1999-2014
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