8

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

Improve this question

1 Answer 1

Reset to default
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).

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

2 Comments

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!
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?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.