Is it possible to give insert with select?

Question

Is it possible to give insert with select?

Navigation

#1 by (6 votes)

4

I have two tables:

hardware(ID, DEVICEID, NAME, ...)

and

softwares(ID, HARDWARE_ID, PUBLISHER, NAME, VERSION, ETC ...).

I need to insert into the softwares table, but I do not have HARDWARE_ID , I only get the hardware name ( hardware.NAME ), this HARDWARE_ID is what makes the relationship between the tables.

I do not want to give select in hardware where name = $software['server_name'] for each record line, I'm creating an API that will include software arrays sent via JSON.

Can anyone help me?

php mysql

asked by anonymous 22.11.2016 / 15:11

1 answer

Referencing class Syntax Razor C # How do I set default properties for a variable that start with a specific text?

score 6 · Accepted Answer

Yes, you can insert by using select .

'INSERT INTO software (HARDWARE_ID, PUBLISHER, NAME, VERSION) 
SELECT ID, 'TEXTO PUBLISHER', 'TEXTO NAME', 'TEXTO VERSION' 
FROM hardware hw WHERE hw.NAME = 'NOME HARDWARE';'

When performing INSERT with select MySQL it will use the SELECT returns as values to be inserted.

Be careful about generating the primary key of the software table, if the key is not generated by a AUTO_INCREMENT or trigger, the return of SELECT should generate a key for each line.

If you want to list more than one hardware at a time instead of WHERE hw.NAME = 'NOME HARDWARE' change the SELECT filter to bring N hardware at a time. WHERE hw.NAME IN ('NOME HARDWARE', 'NOME HARDWARE 2')

MySQL 5.7 DOC