Reputation: 598
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
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.
Upvotes: 1
Views: 528
Reputation: 2179
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.
Upvotes: 2