Adding a columb in a table.

[Paul-D]

Hi. I have an exsisting table Bank_Reason. I want to add an extra columb 'Colour' to it. I am getting an error when I try to change it. I have done a drop table and recreate. Can any help with this extra columb please. I worked fine before. P.S. I have noticed that i was missing a (`). NEW line INSERT INTO `Bank_Reason` (`ID`, `ReasonID`, `Reason`,`Colour`) VALUES. Makes no diffrence.

DROP TABLE IF EXISTS `Bank_Reason`;
CREATE TABLE IF NOT EXISTS `Bank_Reason` (
  `ID` int(11) NOT NULL,
  `ReasonID` int(11) DEFAULT NULL,
  `Reason` varchar(20) DEFAULT NULL,
  `Colour` varchar(8) DEFAULT '#000000'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Bank_Reason`
--

INSERT INTO `Bank_Reason` (`ID`, `ReasonID`, `Reason`,Colour') VALUES
(1, 0, '---SELECT','#000000'),
(2, 1, 'Other',','#0000ff'),
(3, 2, 'Virgin Bank','#000000'),
(4, 3, '_Spare_2','#000000'),
(5, 4, 'CT','#ff00ff'),
(6, 5, 'Energy','#c51010'),
(7, 6, 'Mobile','#27b30b'),
(8, 7, 'Virgin_BB','#06b8b6'),
(9, 8, 'MNOPF','#00aa00'),
(10, 9, 'Water','#aa7700'),
(11, 10, '@Shops','#ff0000'),
(12, 11, 'Online','#7777ff'),
(13, 12, 'Cash','#000000'),
(14, 13, 'Pablo','#000000'),
(15, 14, 'Amazon Prime','#000000'),
(16, 15, 'Ebay/Paypal','#7a061c'),
(17, 16, 'Argos/Store cards','#000000'),
(18, 17, 'Alexa Music','#000000'),
(19, 18, 'HSBC','#aa00aa'),
(20, 19, 'Amazon Orders','#aa7700'),
(21, 20, 'State Pension','#301de8'),
(22, 21, 'Home Insurance','#000000'),
(23, 22, 'Lottery','#000000'),
(24, 23, 'Rent','#000000'),
(25, 24, 'Private Health','#000000'),
(26, 25, 'Credit card **','#000000'),
(27, 26, '_Spare_1','#000000');

 

Edited Wednesday at 02:33 PM by Paul-D

[Barand]

INSERT INTO `Bank_Reason` (`ID`, `ReasonID`, `Reason`,Colour') VALUES
                                                            ^
                                                            |
                                                  unwanted single quote

 

[Paul-D]

I fixed the wrong quote but there is still an error. When I put the code into Notepad++ Line 8 is a blank line under create table section. 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(3, 2, 'Virgin Bank','#000000'),
(4, 3, '_Spare_2','#000000'),
(5, 4, 'CT','#f' at line 8

DROP TABLE IF EXISTS `Bank_Reason`;
CREATE TABLE IF NOT EXISTS `Bank_Reason` (
  `ID` int(11) NOT NULL,
  `ReasonID` int(11) DEFAULT NULL,
  `Reason` varchar(20) DEFAULT NULL,
  `Colour` varchar(8) DEFAULT '#000000'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Bank_Reason`
--

INSERT INTO `Bank_Reason` (`ID`, `ReasonID`, `Reason`,`Colour`) VALUES
(1, 0, '---SELECT','#000000'),
(2, 1, 'Other',','#0000ff'),
(3, 2, 'Virgin Bank','#000000'),
(4, 3, '_Spare_2','#000000'),
(5, 4, 'CT','#ff00ff'),
(6, 5, 'Energy','#c51010'),
(7, 6, 'Mobile','#27b30b'),
(8, 7, 'Virgin_BB','#06b8b6'),
(9, 8, 'MNOPF','#00aa00'),
(10, 9, 'Water','#aa7700'),
(11, 10, '@Shops','#ff0000'),
(12, 11, 'Online','#7777ff'),
(13, 12, 'Cash','#000000'),
(14, 13, 'Pablo','#000000'),
(15, 14, 'Amazon Prime','#000000'),
(16, 15, 'Ebay/Paypal','#7a061c'),
(17, 16, 'Argos/Store cards','#000000'),
(18, 17, 'Alexa Music','#000000'),
(19, 18, 'HSBC','#aa00aa'),
(20, 19, 'Amazon Orders','#aa7700'),
(21, 20, 'State Pension','#301de8'),
(22, 21, 'Home Insurance','#000000'),
(23, 22, 'Lottery','#000000'),
(24, 23, 'Rent','#000000'),
(25, 24, 'Private Health','#000000'),
(26, 25, 'Credit card **','#000000'),
(27, 26, '_Spare_1','#000000');

 

Edited Wednesday at 02:59 PM by Paul-D

[Barand]

 

INSERT INTO `Bank_Reason` (`ID`, `ReasonID`, `Reason`,`Colour`) VALUES
(1, 0, '---SELECT','#000000'),
(2, 1, 'Other',','#0000ff'),
               ^^
             Again!?

 

[gizmola]

Quote

ENGINE=MyISAM

I strongly recommend that you change all of your tables to use the InnoDB engine.    It's currently the default engine, given that it provides superior performance and features.  

Any aspect of a table can be changed (no dropping required) using the ALTER TABLE syntax.