How to perform insert and select operation simultaneously on a same table in MySQL query?

Question

I've a table called 'questions' in my database. For your reference I'm specifying below the structure of table 'questions':

question_id                   bigint(12) AUTO_INCREMENT (Primary Key)
question_parent_id            bigint(12)
question_subject_id           smallint(11)
question_topic_id             int(11)
question_directions           text
question_text                 text
question_file                 varchar(100)
question_description          text
question_difficulty_type      tinyint(4)
question_has_sub_ques         enum('0', '1')
question_picked_individually  enum('no', 'yes')
question_appeared_count       bigint(12)
question_manual               enum('0', '1')
question_site_id              varchar(10)
question_created_staff_id     varchar(32)
question_added_date           bigint(12)
question_updated_staff_id     varchar(32)
question_updated_date         bigint(12)

This table is containing thousands of questions. Now the scenario is I'm taking few values from the PHP form as follows:

/*Following are the subject id and topic id from which I want to fetch the questions belonging to that subjet id and topic id */    
    $_POST['from_subject_id'] => 8
    $_POST['from_topic_id'] => 545
/*Following are the subject id and topic id to which I want to add the questions fetched in above query*/
    $_POST['to_subject_id'] => 8
    $_POST['to_topic_id'] => 547

So I want to add the questions fetched based on the first two values of subject_id and topic_id(i.e. based on $_POST['from_subject_id'] and $_POST['from_topic_id']) to the same table. But all of these newly inserted questions should have subject_id and topic_id values given after next comment(i.e. $_POST['to_subject_id'] and $_POST['to_topic_id']). In short I want to perform Insert and select operation simultaneously on a same table. For achieving this I tried lots of tricks as well as googled it for the solution but not able to find the perfect solution. Can anyone please help me in this regard? I tried with the following SQL query but it is inserting the same questions with the subject and topic id values which they already have. In short the questions are getting repeated and I don't want that result. Instead I want same questions to be inserted with new subject_id and new topic_id. For your reference I'm giving below the SQL query:

INSERT INTO questions (question_parent_id, question_subject_id, question_topic_id, question_directions, question_text, question_file, question_description, question_difficulty_type, question_has_sub_ques, question_picked_individually, question_manual, question_site_id, question_created_staff_id, question_added_date, question_appeared_count, question_updated_staff_id, question_updated_date)
SELECT question_parent_id, question_directions, question_text, question_file, question_description, question_difficulty_type, question_has_sub_ques, question_picked_individually, question_manual, question_site_id, question_created_staff_id, question_added_date, question_appeared_count, question_updated_staff_id, question_updated_date
FROM questions
WHERE question_subject_id='8' AND question_topic_id='545'

Thank you so much for

Answer 1

Substitute the values you want into the select part of the query:

INSERT INTO questions (question_parent_id, question_subject_id, question_topic_id, question_directions, question_text, question_file, question_description, question_difficulty_type, question_has_sub_ques, question_picked_individually, question_manual, question_site_id, question_created_staff_id, question_added_date, question_appeared_count, question_updated_staff_id, question_updated_date)
    SELECT question_parent_id,  8, 547,
           question_directions, question_text, question_file,
           question_description, question_difficulty_type, question_has_sub_ques,
           question_picked_individually, question_manual, question_site_id,
           question_created_staff_id, question_added_date, question_appeared_count,
           question_updated_staff_id, question_updated_date
FROM questions
WHERE question_subject_id = '8' AND question_topic_id = '545';

Or, the select might begin:

select question_parent_id, $_POST['to_subject_id'], $_POST['to_topic_id']

Answer 2

I'm not sure I understand your question. You want to add new records to the same table, but changing the topic_id from 545 to 547? If that is all you want the following will work:

INSERT INTO questions 
SELECT 
    question_id ,
    question_parent_id ,
    question_subject_id + 1 ,
    547 ,
    question_directions ,
    question_text   ,
    question_file   ,
    question_description    ,
    question_difficulty_type    ,
    question_has_sub_ques   ,
    question_picked_individually    ,
    question_appeared_count ,
    question_manual ,
    question_site_id    ,
    question_created_staff_id   ,
    question_added_date ,
    question_updated_staff_id   ,
    question_updated_date   
FROM questions
WHERE question_subject_id=8 AND question_topic_id=545;

Some questions for further clarification: Is there some relationship between the newly inserted question_parent_id and the existing row's question_id? Or does the new row just inherit the old values of question_id & question_parent_id?

You say you want a "new" subject id, but you don't specify what it should be. I simply incremented the subject id from the existing row by adding 1 to it. If you want something else, please specify. In your example from the PHP, it appears that it is supposed to remain 8. In that case remove the +1 from my above code.