create - select statement
Posted by:
S Alston
Date: April 05, 2005 11:33AM
Hi,
I am using the current query
insert into t_invoice(invoice_start_date, invoice_end_date, name, faces, hrs,hrly_rate, grade,session_type, service_date, price, invoice_num)
select '05-3-1', '05-3-31', name, faces, hrs, hrly_rate, grade, session_type, service_date, hrly_rate * hrs, 05331 from
t_billing where service_date between '05-3-1' and '05-3-31' and faces = 111111;
The table t_invoice is completely empty. However, I receive the message "ERROR 1062 (23000): Duplicate entry '5331-111111' for key 1".
My table looks like the following:
t_invoice | CREATE TABLE `t_invoice` (
`invoice_num` int(8) default NULL,
`name` varchar(42) default NULL,
`tutor_id` int(9) default NULL,
`faces` int(6) default NULL,
`session_type` varchar(20) default NULL,
`special` varchar(20) default NULL,
`grade` char(2) default NULL,
`invoice_end_date` date default NULL,
`invoice_start_date` date default NULL,
`service_date` datetime default NULL,
`start_time` datetime default NULL,
`end_time` datetime default NULL,
`type` char(1) default NULL,
`hrs` float(5,2) default NULL,
`trips` int(5) default NULL,
`hrly_rate` float(5,2) default NULL,
`price` float(7,2) default NULL,
`date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_
TIMESTAMP,
UNIQUE KEY `uk_key` (`invoice_num`,`faces`)
ENGINE=InnoDB DEFAULT CHARSET=latin1 |
-----------+-------------------------------------------------------------------
Does anyone have any ideas why this would be happening??? I tried using a multiple primary key and had the same problem.
Thanks in advance for any help