Inserting new customer into table using subquery

Question

Insert a customer Jake Smith whose shipping address is “yknhl fr hluhw”, he can be reached at ‘1236548780’ & ‘[email protected]’ (use a sub query to get the address from shipping_details table, direct insertion of address will be marked as incorrect).

^ the assignment question's instructions

use assignment;   
insert into customers(first_name,last_name,email,phoneNo)    
values ('Jake','Smith','[email protected]','1236548780');
where shipping_detail_id in (select shipping_details.address from shipping_details where address like 'yknhl fr hluhw');

What am I doing wrong in line 4 so I can successfully add the new customer into the tables. I feel confused and not sure where to go from there

Answer 1

A s you can see in the definition of ÌNSERT INTO

There is no WHERE clause, a UPDATE has one

But you can use

use assignment;   
insert into customers(first_name,last_name,email,phoneNo,shipping_detail_id)    
SELECT('Jake','Smith','[email protected]','1236548780', shipping_detail_id)
 from shipping_details where address like 'yknhl fr hluhw'
 LIMIT 1;

OR

use assignment;   
insert into customers(first_name,last_name,email,phoneNo,shipping_detail_id)    
VALUES ('Jake','Smith','[email protected]','1236548780', (SELECT shipping_detail_id
 from shipping_details where address like 'yknhl fr hluhw'
 LIMIT 1));

The LIMIT 1 is in case that there are multiple ids for that adress, then you should add an ORDER BY to get the correct one