CODEWITHSUNDEEP
mysqlsql
Ganesh Salunkhe
Ganesh Salunkhe

Reputation: 598

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

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

Answers (1)

Piyush Gupta
Piyush Gupta

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

Related Questions