How to insert data into MySQL table?

Question

I have 3 tables.

A users table with just an id and a name.

A students table with id, name and subject.

A students_enrolled table which is now empty.

I want find all table entries from 'users' table which have matches in 'students' table. For this I have the following query:

SELECT  *
FROM    students
WHERE   name  IN (SELECT name FROM users);

QUERY RESULTS:

id  name
3   mark
4   steve

This works but now the GOAL is to pupulate the third table ('students_enrolled') with this data and have the ids match the users table.

In the users table 'mark' is ID 3 and 'steve' is ID 4, but in the students table they are IDs 50 and 99 respectively. I want to insert them into the 'students_enrolled' table with matches IDs to the users table. Note: the 'names' will always be unique - there will never be two steves within one table. Also, if a match is found I would like the 'status' field to have the entry 'enrolled'.

The goal is to have the 'students_enrolled' table show this:

id  name    status  subject
3   mark    enrolled    math
4   steve   enrolled    reading

I have created a sqlfiddle with all the schema and data if someone has any ideas here:

[SQL Fiddle Link][1]

Schema:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `name`)
VALUES
    (1, 'john'),
    (2, 'jane'),
    (3, 'mark'),
    (4, 'steve');

-- ----------------

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


INSERT INTO `students` (`id`, `name`, `subject`)
VALUES
    (50, 'mark', 'math'),
    (99, 'steve', 'reading');


-- ----------------

CREATE TABLE `students_enrolled` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


/* Desired result:*/
/* INSERT INTO `students_enrolled` (`id`, `name`, `status`, `subject`)
VALUES
    (3, 'mark', 'enrolled', 'math'),
    (4, 'steve', 'enrolled', 'reading'); */

-- ----------------

It should be user_id as foreign key, instead of creating the name column everywhere — hjpotter92
– hjpotter92, Commented Oct 2, 2015 at 17:30

Rahul · Accepted Answer · 2015-10-02 17:31:41Z

1

Use INSERT INTO .. SELECT FROM construct like

INSERT INTO students_enrolled (name,subject)
SELECT  s.name, s.subject
FROM    students s
JOIN users u ON s.name = u.name;

answered Oct 2, 2015 at 17:31

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ven Over a year ago

Thanks Rahul! You got me almost all the way there.

ven · Accepted Answer · 2015-10-02 17:57:53Z

0

Here is a fully working query:

INSERT IGNORE INTO `students_enrolled` (`id`, `name`, `status`, `subject`)
SELECT u.`id`, s.`name`, 'enrolled', s.`subject`
FROM `students` AS s
INNER JOIN `users` AS u ON s.`name` = u.`name`;


SELECT *
FROM `students_enrolled`;

Cheers!

answered Oct 2, 2015 at 17:57

ven

4053 gold badges10 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to insert data into MySQL table?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related