CODEWITHSUNDEEP
mysql
Rose Fox
Rose Fox

Reputation: 39

Increment a query result display precisely once

A volunteer gig is requiring me to wrestle with a MySQL database, which I've never done before, so please accept my apologies for the probably very basic nature of my question! I've read through a lot of the similar questions about putting query results into variables, but most of them either aren't relevant or go right over my head.

Users who will be attending a conference have answered a survey to provide information on when they're available. I need to copy this data into the entirely separate table that the schedule-creation software uses. Fortunately I set up the survey to store each answer in the form of the software's time slot ID number.

I've got a query that begins as follows:

INSERT INTO user_time_slots (user_id,day_time_slot_id,available)
SELECT users.id, question.answer, '1'
...

The result is rows like 

455,17,1
455,29,1
466,13,1

So far so good. However! Those are whole-hour time slots, like 12:00 and 15:00, and the conference has some items scheduled on the half-hour. Each half-hour time slot has an ID number of the whole-hour time slot + 1. Assuming anyone available at 12:00 is also available at 12:30, I'd like to get results that look like

455,17,1
455,18,1
455,29,1
455,30,1
466,13,1
466,14,1

I get as far as @half_hour := question.answer + 1, and then I'm lost; I don't know how to alternate between setting day_time_slot_id to question.answer and setting it to @half_hour. Is this doable without ridiculous contortions?

If not, my backup plan is to run the query once as above and once as

INSERT INTO user_time_slots (user_id,day_time_slot_id,available)
SELECT users.id, @half_hour := question.answer + 1, '1'
...

But of course I'd rather have one query than two.

Any help would be greatly appreciated. Thank you!

Cheers, Rose

Upvotes: 1

Views: 65

Answers (2)

peterm
peterm

Reputation: 92785

Try

INSERT INTO user_time_slots (user_id,day_time_slot_id,available)
SELECT users.id, question.answer, '1'
...
UNION ALL
SELECT users.id, question.answer + 1, '1'
...

Upvotes: 1

Barmar
Barmar

Reputation: 780724

You need to use UNION to generate multiple sets of rows:

INSERT INTO user_time_slots (user_id, day_time_slot_id)
SELECT users.id, question.answer, '1'
...
UNION
SELECT users.id, question.answer+1, '1'
...

If those WHERE clauses are expensive, another way is with a JOIN:

INSERT into user_time_slots (user_id, day_time_slot_id)
SELECT users.id, question.answer + increment, '1'
...
JOIN (SELECT 0 increment UNION SELECT 1 increment) x
...

Upvotes: 1

Related Questions