CODEWITHSUNDEEP
mysqlsqljoinselectsql-insert
Vinay
Vinay

Reputation: 11

select multiple rows form a table and insert into a different table

I have the following tables..

Passengers:

| Reservation_number | Passport_number | Full_name |
|--------------------|-----------------|-----------|
| 1                  | 1               | abc       |
| 1                  | 2               | def       |
| 1                  | 3               | ghi       |

Booking:

| Booking_id | Reservation_number | Card_number | Price |
|------------|--------------------|-------------|-------|
| 1          | 1                  | 6767        | 460   |

Ticket:

| Booking_id | Passport_number | Ticket_number |
|------------|-----------------|---------------|
|            |                 |               |
|            |                 |               |

From Passengers,i want to select all the Passport_number's for which Reservation_number is 1(here it is all the 3 rows) and then insert each of those Passport_number's into each row of Ticket table. After that, all the rows for Booking_id in Ticket table must be set to 1 since the Booking_id is 1 for Reservation_number=1(this can be seen in Booking table). How can i do it?

Upvotes: 1

Views: 39

Answers (1)

GMB
GMB

Reputation: 222572

Consider the insert ... select syntax.

You can join booking and passengers, and insert the resultset into ticket as follows:

insert into ticket(booking_id, passport_number, ticket_number)
select b.booking_id, b.reservation_number, p.passport_number
from booking b
inner join passengers p on p.reservation_number = b.reservation_number

Upvotes: 1

Related Questions