When incoming rows contain a medication code, email address, or other business key instead of a numeric foreign key, resolve the IDs in a set-based query. In MySQL, INSERT ... SELECT can join the incoming values to the parent table and insert all valid rows at once.
Last updated: September 29, 2026.
INSERT INTO orders (customer_id, medication_id, quantity)
SELECT incoming.customer_id, medication.id, incoming.quantity
FROM (
SELECT 101 AS customer_id, 'AMOX500' AS medication_code, 2 AS quantity
UNION ALL
SELECT 102, 'IBU200', 1
) AS incoming
JOIN medication
ON medication.code = incoming.medication_code;Put a unique index on medication.code. Without uniqueness, one incoming row can join multiple parent rows and create duplicate orders; without a match, an inner join silently omits that row.
Detect missing parent rows first
Run the same source query with a LEFT JOIN and filter with WHERE medication.id IS NULL. Treat any result as rejected input and report its business key. This prevents a batch from appearing successful when some rows were skipped. If every input row must succeed together, perform validation and insertion in a transaction.
Use the right source for larger batches
A derived table is convenient for a short example. For real imports, load incoming data into a staging table with a batch identifier, validate types and required fields, then join that staging table to the parent table. MySQL’s INSERT ... SELECT documentation describes the supported syntax and restrictions.
Use parameterized statements when populating the staging table; do not concatenate incoming values into SQL. The application should send stable business keys, while the database owns the numeric IDs and enforces the foreign key.
Verify counts and constraints
Compare the source-row count, missing-match count, and inserted-row count before committing. Keep the foreign-key constraint enabled: it catches incorrect IDs that application checks miss. Review the basic SQL INSERT syntax, then use the MySQL collation guide if text keys fail to match because of incompatible definitions. For PHP callers, start with a prepared MySQL connection.
