Click here to Skip to main content
11,575,502 members (57,633 online)
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 22:37pm
Edited 27-Feb-13 1: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


Advertise | Privacy | Mobile
Web03 | 2.8.150624.2 | Last Updated 27 Feb 2013
Copyright © CodeProject, 1999-2015
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