Insert id in sql table based on another particular value in another table

Question

I am stuck in a situation wherein I have to insert id in sql table based on another particular value in another table.

For example: I have one table say bookings with column invoice_number and another table say invoice_hotels with column invoice_id.

I am trying to insert invoice_id in invoice_hotels based on data in invoice_number column of bookings table.

I want to achieve below functionality also use MySQL squery only not PHP

If Last value in invoice_number column of bookings table is 9000 then I want to insert 9001 in invoice_id column of invoice_hotels

I know this can easily be done through PHP script but I am trying to do it with sql only.

I have referred this and this but didn't understood how to use it.

get the last insert id from 1st table and add +1 to that and then insert it in the second table — Arun Kumaresh, Apr 02 '16 at 06:41
How can I do it with only sql? I mean without using PHP select query. — Ganesh Salunkhe, Apr 02 '16 at 06:42
insert into tablename values(SELECT IFNULL(MAX(invoice_id)+1, 1) from bookings); — Piyush Gupta, Apr 02 '16 at 06:52
Solution suggested by you will surely work for me but I am trying to do it with triggers. — Ganesh Salunkhe, Apr 06 '16 at 13:21
If it is working then you should accept this answer, it will help to others user also..... and if you having another doubt then you should ask another question with your all requirement :) — Piyush Gupta, Apr 06 '16 at 13:26

Piyush Gupta · Answer 1 · 2016-04-02T07:07:16.553

You can try this query:

here we are geting max id for invoice_number and then will +1 increment with help of select query after that you can insert this value to another table with help of insert query.

INSERT INTO table_name ( field1, field2,...fieldN )                       
 VALUES((SELECT IFNULL(MAX(invoice_number)+1, 1) from bookings), value2,...valueN);

IFNULL(MAX,1) meaning if id is null then it will take by default id=1

NOTE: I'm not sure about tablename and column name so you can adjust according your requirement.

Insert id in sql table based on another particular value in another table

1 Answers1