• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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
0
Eaddy Barnes
Asked:
Eaddy Barnes
1 Solution
 
jcott28Commented:
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now