CODEWITHSUNDEEP
phpmysqlsqldatabasesql-insert
Rocstar
Rocstar

Reputation: 1487

Insert Data From One Table to Another Table and Add New Values

I have two tables. Temporary table : 

CREATE TABLE IF NOT EXISTS `temporary` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `FK_user` int(11) NOT NULL,
  `FK_bin` varchar(50) NOT NULL,
  `orderDate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;

And orders table : 

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL,
  `FK_user` int(11) NOT NULL,
  `FK_bin` varchar(50) NOT NULL,
  `orderNumber` varchar(11) NOT NULL,
  `orderDate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to insert all values of Temporary table to Orders table and add orderNumber manually with this : uniqid(rand()) .

I have use INSERT INTO orders SELECT * FROM temporary WHERE FK_user = ? But they don't work because orderNumber don't exist in Temporary table ...

How I can do ? please

Upvotes: 1

Views: 99

Answers (1)

Tajinder
Tajinder

Reputation: 2348

Hoping i understand your questioin correctly. I think below query will help.

INSERT INTO orders(id , FK_user , FK_bin , orderNumber , orderDate) 
(SELECT id , FK_user , FK_bin , uniqid(rand()) AS orderNumber , orderDate 
 FROM temporary WHERE FK_user = ?);

Use your function to generate order number in select statement as mentioned in above query.

Upvotes: 2

Related Questions