Insert/Altering Tables

Hello Folks,

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 ;

Open in new window

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;

Open in new window


 
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

Open in new window



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
LVL 11
Eaddy BarnesITAsked:
Who is Participating?
 
jcott28Connect With a Mentor Commented:
Not that I'm aware of.  Not null is not null.  I would recommend if you want to do this, just pass in some hardcoded value that wouldn't normally be used, like -1 or something.  Then you can update the table later where the value = -1.
0
 
Eaddy BarnesITAuthor Commented:
Thanks, that did the trick..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.