CODEWITHSUNDEEP
mysql
DavidM
DavidM

Reputation: 31

MySQL query to insert record into table based on records in another table

I need some help with a MySQL (5.6) query. I'm not sure how to begin.

I have a table "blades". This table contains blade server records, including the following columns:

Each enclosure holds 16 blades servers in bays 1-16.

I have another table "available_bays". This table contains the following columns:


I need a query that will scan through the blades table and for each enclosure that has less than 16 blades, write a row to the available_bays table with enclosure_name and bay_number. The used enclosure bays may not be consecutive. In other words, the servers in enclosure1 may be in bays 1-8, 10, 13-16. So in this example, I would need to end up with the following records in the available_bays table:

enclosure_name bay_number
enclosure01 9
enclosure01 11
enclosure01 12

Also, it would need to insert the record in the available_bays table only if the same enclosure_name and bay_number do not already exit.

Any help on creating this query would be greatly appreciated.

Upvotes: 0

Views: 121

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

sqlFiddle is an example here I am only generating available_bays for the first 3 bays, you can add in the the UNION SELECT 4 and so on up to 16.

INSERT INTO available_bays(enclosure_name,bay_number)
SELECT t3.enclosure_name,t3.bay_number
FROM
  (SELECT enclosure_name,bay_number FROM
    (      SELECT 1 as bay_number
     UNION SELECT 2
     UNION SELECT 3
     UNION SELECT 4
     UNION SELECT 5
     UNION SELECT 6
     UNION SELECT 7
     UNION SELECT 8
     UNION SELECT 9
     UNION SELECT 10
     UNION SELECT 11
     UNION SELECT 12
     UNION SELECT 13
     UNION SELECT 14
     UNION SELECT 15
     UNION SELECT 16)as t,
    (SELECT DISTINCT enclosure_name FROM blades)as t2
  )as t3
LEFT JOIN blades b
ON (b.bay_number = t3.bay_number AND b.enclosure_name = t3.enclosure_name)
LEFT JOIN available_bays ab
ON (ab.bay_number = t3.bay_number AND ab.enclosure_name = t3.enclosure_name)
WHERE  b.bay_number IS NULL
  AND ab.bay_number IS NULL;

Upvotes: 1

Related Questions