mysql insert information from multiple rows from another table

Question

I am using MySQL version 5.6. I have a table event that takes two ids and a date, and a table student that contains a column id. I want to insert two ids when the name match (WHERE firstname =.., AND lastname=..,). However, I don't quite know how to take id from two rows in one insert command. Can any one help me please?

Thanks, Paul

Gordon Linoff · Accepted Answer · 2013-06-06 14:47:16Z

18

If you are trying to insert into a table, then you want to use insert. To get data from another table or query, you would want the insert . . . select form.

From what you say, this seems something like what you want:

insert into event(id1, id2, date)
    select s1.id, s2.id, now()
    from student s1 cross join
         student s2
    where s1.id <> s2.id and
          s1.firstname = firstname and s1.lastname = lastname and
          s2.firstname = firstname and s2.lastname = lastname;

It would return all pairs of students where the names match (but don't have the same id).

answered Jun 6, 2013 at 14:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

verticese Over a year ago

This is not quite what I asked for, probably due to my ambiguous question. But I worked from it and got what I wanted. So thank you very much!

verticese Over a year ago

One more question: I noticed that if I had duplicates in my table it would cross count each one multiple times and the result will be, if 8 duplicates for example, 64 rows. I guess this is because of the "cross join" or something not set in where clause. Is there a way to not select the ones that I have selected in previous rows in a query?

Collectives™ on Stack Overflow

mysql insert information from multiple rows from another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related