insert value of single column

Question

In my MySQL database I have two tables: customers and feedbacks

Values come from a small php app.

To show to which customer a feedback record belongs, I want to insert the values of customers.customerID into feedbacks.customerID

Only single column (customer.customerID) into (feedback.cutomerID). Here is the DDL

@strawbery
-- Table structure for table `customers`
--

CREATE TABLE IF NOT EXISTS `customers` (
  `customerID` int(20) NOT NULL AUTO_INCREMENT,
  `compid` varchar(5) DEFAULT NULL,
  `store` varchar(10) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `contact` varchar(15) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL,
  `gender` varchar(7) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `fbdate` varchar(10) DEFAULT NULL,
  `sysip` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`customerID`),
  UNIQUE KEY `customerID` (`customerID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
CREATE TABLE IF NOT EXISTS `feedback` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customerID` int(20) NOT NULL,
  `design` varchar(10) NOT NULL,
  `variety` varchar(10) NOT NULL,
  `fabric` varchar(10) NOT NULL,
  `stitch` varchar(10) NOT NULL,
  `size` varchar(10) NOT NULL,
  `service` varchar(10) NOT NULL,
  `experience` varchar(10) NOT NULL,
  `recommend` varchar(20) NOT NULL,
  `comments` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_feedback` (`customerID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

regards

Can we see the proper DDLs for both tables.

Strawberry
– Strawberry

2015-02-10 12:38:54 +00:00
Commented Feb 10, 2015 at 12:38 — Strawberry
– Strawberry, Commented Feb 10, 2015 at 12:38

CD001 · Accepted Answer · 2015-02-10 13:12:52Z

Essentially you'll need to create the customer first - if they don't exist already.

INSERT INTO customers ( ... ) VALUES ( ... );

Then insert the feedback.

INSERT INTO feedback (customerID, ... ) VALUES (LAST_INSERT_ID(), ... );

However, with MySQL you can use a non-standard ON DUPLICATE KEY UPDATE trick if your customer already exists in the database:

INSERT INTO customers ( ... ) VALUES ( ... )
ON DUPLICATE KEY UPDATE customerID = LAST_INSERT_ID(customerID)

This will effectively assign the result from LAST_INSERT_ID() to being the specified customer id, if the customer already exists, as per http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html (at the bottom of the page) - if the customer didn't exist, you'll get the auto-incremented primary id for the newly created customer as you'd expect.

Then your INSERT INTO feedback query should work fine with LAST_INSERT_ID() irrespective as to whether the customer was created or updated - or you can do it at the application level with something like PDO::lastinsertid http://php.net/manual/en/pdo.lastinsertid.php

collapsar · Accepted Answer · 2015-02-10 12:41:59Z

0

Insert the ids from customer that aren't yet in your feedbacks table.

INSERT
  INTO feedbacks ( customerID )
SELECT c.customerID
  FROM customers c
 WHERE c.customerID NOT IN (
           SELECT f.customerID
             FROM feedbacks
       )
     ;

answered Feb 10, 2015 at 12:41

collapsar

17.4k5 gold badges40 silver badges66 bronze badges

6 Comments

Amjad Zahid Over a year ago

I tried it collapsar,,, but it inserts a separate record other than feedback record each time. I mean, first the insert query execute to insert feedback and then this query run to insert customerID in new record. I want it to be inserted in the same record.

CD001 Over a year ago

... and if there are 1000 customers and 20000 feedback lines already in the database? I get the feeling this isn't a job for a "one-off" update.

Amjad Zahid Over a year ago

no, one customer can insert one feedback at a time.

timbmg Over a year ago

@AmjadZahid so with you comment it is no more clear what you are asking.

Amjad Zahid Over a year ago

@blckbird, I give you a tab running my feedback app. you enter your name, contact, email, age, gender and address[this info goes to table customers]. then you use the ui for design, variety, fabric etc of my store and submit. This info goes to table feedbacks. Now what I want is the customerID generated for you in table customers should be inserted into feedbacks.customerID where your feedback about my store is inserted. Is it clear? :)

|

Collectives™ on Stack Overflow

insert value of single column

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related