Is it possible to give Insert with select?

Asked

Viewed 210 times

4

I have two tables:

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

and

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

I need to give insert in the software table, but I do not have the HARDWARE_ID, I only get the name of the hardware (hardware.NAME), that HARDWARE_ID is what makes the relationship between the tables.

I don’t want to select the hardware where name = $software['server_name'] for each record line, I am creating an API that will include arrays of software sent via JSON.

Someone can help me?

php mysql

1 answer

7


Yes, it is possible to perform a insert using a 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 the INSERT select Mysql will use returns from SELECT as values to be inserted.

Take care of the generation of primary table software key, 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 relate more than one hardware at a time than WHERE hw.NAME = 'NOME HARDWARE' change the filter of SELECT to bring N hardware at a time. WHERE hw.NAME IN ('NOME HARDWARE', 'NOME HARDWARE 2')

Mysql 5.7 DOC

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.