CODEWITHSUNDEEP
mysqlinsert
ven
ven

Reputation: 405

How to insert data into MySQL table?

I have 3 tables.

  1. A users table with just an id and a name.
  2. A students table with id, name and subject.
  3. 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'); */

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

Upvotes: 0

Views: 65

Answers (2)

ven
ven

Reputation: 405

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!

Upvotes: 0

Rahul
Rahul

Reputation: 77876

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;

Upvotes: 1

Related Questions