CODEWITHSUNDEEP
mysql
ShyProgrammer
ShyProgrammer

Reputation: 1

MySQL data from one table to other

I have 3 tables in MySQL, structure as below.

Table 1: medicines

Fields: med_id med_name

Sample Data:

med_id  | med_name
1       | Panadol
2       | Tylenol
3       | Brufen

Table 2: transactions

Fields: tr_id, tr_medicine, tr_city

Sample Data:

tr_id   | tr_medicine   | tr_city
1       | Panadol   | London
2       | Tylenol   | Milan
3       | Brufen    | New York

Table 3: summary

Fields: sm_id, sm_medicine

Sample Data:

sm_id   | sm_medicine
1       | 1
2       | 2
3       | 3

What I want to do is, in one query (sub query may be used), ready data from 'transactions' table and insert it into 'summary' table, but instead medicine name, medicine id should be inserted. Medicine id can be fetched from 'medicines' table.

Upvotes: 0

Views: 35

Answers (1)

KAD
KAD

Reputation: 11122

You can do this using INSERT INTO table SELECT from other table and with the help of nested selects:

INSERT INTO summary (sm_id, sm_medicine)
SELECT (SELECT MAX(sm_id)+1 FROM summary), 
        (SELECT med_id FROM medicines WHERE med_name = transactions.tr_medicnie)
FROM transactions;

First you get the maximum id in the summary and increase it with one (SELECT MAX(sm_id)+1 FROM summary), this is not needed if AUTO_INCREMENT is set to the column.

Second nested select (SELECT med_id FROM medicines WHERE med_name = transactions.tr_medicnie) it imitates a join between table medicines and transactions where you get the medicine id in each transaction and insert it in summary.

Upvotes: 1

Related Questions