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

Question

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:

enclosure_name
bay_number
description
server_name
...

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

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

enclosure_name
bay_number
status
reserved
reserved_by
reserved_date

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.

Use an INSERT IGNORE INTO available_bays SELECT ... query. — Barmar
– Barmar, Commented Dec 14, 2013 at 0:38
That doesn't seem like the work for 1 query... Sounds like you need more logic than SQL can give you. — Rudie
– Rudie, Commented Dec 14, 2013 at 0:40
Do you have any control over the table design? Ideally you would have a table which specified all the bays, filled or not. — sahmeepee
– sahmeepee, Commented Dec 14, 2013 at 0:41
The blades table data is generated by a powershell script that pulls the server data from a HP tool. It does not have the option to insert a blank record for an empty server bay. — DavidM
– DavidM, Commented Dec 14, 2013 at 0:58
is it possible for a SQL query to insert records into the blades table for any enclosure/bay_number that do not already exist? — DavidM
– DavidM, Commented Dec 14, 2013 at 0:59

Tin Tran · Accepted Answer · 2013-12-14 05:50:33Z

1

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;

edited Dec 14, 2013 at 5:50

answered Dec 14, 2013 at 1:01

Tin Tran

6,1763 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DavidM Over a year ago

Hi, thanks for this query. It works in SQL Fiddle, but I'm getting the following error when I run it in MySQL Workbench: "Error Code: 1292. Truncated incorrect DOUBLE value: '1'"

Tin Tran Over a year ago

what type is bay_number? is it a string?

DavidM Over a year ago

Okay, I found the issue. bay_number datatype is varchar instead of int. So this query does exactly what I need with one exception. If I run it multiple times, it will insert duplicate rows. How can I modify it to not insert the row if the enclosure_name and baynumber already exist in the available_bays table?

Tin Tran Over a year ago

I just added another LEFT JOIN and AND ab.bay_number IS NULL check the updated query code, this will check to see if it's not already in available_bays table.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related