Eaddy Barnes
asked on
Insert/Altering Tables
Hello Folks,
I have 2 Tables:
When i use above query i get an error " #1364 - Field 'bestseller' doesn't have a default value "
I know why the error pops up this is because in the table(table#1) that im inserting into the bestseller field needs a default value for the rows that are inserting.. If u look i have 7 other fields that have "NOT NULL" also..
My question is, is it possible that i can insert the rows now and then alter the table later to put in the default values?
thanks in advance
I have 2 Tables:
CREATE TABLE IF NOT EXISTS `products_main` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`categories` varchar(255) DEFAULT NULL,
`manufacture_id` int(11) unsigned DEFAULT NULL,
`name` varchar(250) DEFAULT NULL,
`DE SKU` varchar(100) NOT NULL,
`price` double unsigned DEFAULT NULL,
`status` tinyint(3) unsigned DEFAULT '0',
`weight` double unsigned DEFAULT NULL,
`description` text,
`product_features` text,
`sml_image_name` varchar(100) DEFAULT NULL,
`sml_image_path` varchar(100) DEFAULT NULL,
`lrg_image_name` varchar(100) DEFAULT NULL,
`lrg_image_path` varchar(100) DEFAULT NULL,
`popular_buys` tinyint(3) DEFAULT NULL,
`large_alt_tag` varchar(128) DEFAULT NULL,
`featured` tinyint(3) unsigned DEFAULT '0',
`date_added` datetime DEFAULT NULL,
`date_updated` datetime DEFAULT NULL,
`bestseller` tinyint(1) NOT NULL,
`sizes` varchar(255) NOT NULL,
`sort_order` int(11) NOT NULL,
`related` varchar(255) NOT NULL,
`promo_icon` int(11) NOT NULL,
`promo_enable` tinyint(1) NOT NULL,
`product_url` varchar(255) NOT NULL,
`ware_house_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`),
KEY `_ind` (`categories`,`manufacture_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
&
CREATE TABLE IF NOT EXISTS `products_dummy` (
`Categories` int(3) NOT NULL DEFAULT '0',
`NAME` varchar(250) DEFAULT NULL,
`DE SKU` varchar(100) NOT NULL,
`Price` double NOT NULL,
`STATUS` int(0) NOT NULL DEFAULT '0',
`Weight` double NOT NULL,
`description` longtext,
`FEATURED` int(0) NOT NULL DEFAULT '0',
`Product_URL` varchar(250) DEFAULT NULL,
`Sml_Image_Path` varchar(110) DEFAULT NULL,
`Lrg_Image_Path` varchar(111) DEFAULT NULL,
`Popular_Buys` int(0) NOT NULL DEFAULT '0',
`Ware_house_id` int(0) NOT NULL DEFAULT '0',
PRIMARY KEY (`DE SKU`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now I want to insert rows from Table#2 into Table#1 using the following query:
insert into products_main (Categories, NAME, `DE SKU`, Price, STATUS, Weight,
DESCRIPTION, FEATURED, `Product_URL`, `Sml_Image_Path`, `Lrg_Image_Path`, `Popular_Buys`,
Ware_house_id) SELECT Categories, NAME, `DE SKU`, Price, STATUS, Weight,
DESCRIPTION, FEATURED, `Product_URL`, `Sml_Image_Path`, `Lrg_Image_Path`,
`Popular_Buys`, Ware_house_id FROM products_dummy WHERE Categories IS NOT NULL
When i use above query i get an error " #1364 - Field 'bestseller' doesn't have a default value "
I know why the error pops up this is because in the table(table#1) that im inserting into the bestseller field needs a default value for the rows that are inserting.. If u look i have 7 other fields that have "NOT NULL" also..
My question is, is it possible that i can insert the rows now and then alter the table later to put in the default values?
thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER