Mysql – Insert incoming data (and pk from 2nd table) into 1st table if true boolean value from 2nd table

MySQL

This question is further to my previous question answered very well by @ypercube here

I have been trying to work out the easiest way to ask the question without ambiguity, here I go…

I have data arriving from a sensor that contains a unique serial number and a data value. The sensor configuration along with its unique serial number is in a table called device which also contains other settings such as enabled or disabled.

I want to store the incoming data value into another table named datain along with the primary key from the device table.

I am currently doing this with several individual MySQL statements and wish to improve the efficiency by reducing it to one MySQL statement.

Steps to complete are:

Retrieve the primary key and boolean enabled state for supplied sensor serial number from device table
Test if primary key exists and boolean enabled value is true, if either is no, then just exit.
If primary key exists and boolean enabled is true, then store the incoming data value and the primary key (identified by the above test) into another table named datain

Maybe below explains a little better what I am trying to do. I know this is not valid MySQL, just an example of what want.

`INSERT INTO mydb.datain(sensorpk,sensorvalue) values (pk from sensor serial in device table, 25.4) WHERE mydb.device.pk =  pk from sensor serial in device table AND mydb.device.enabled = TRUE;`

Best Answer

I'm reading your question differently than Stefan did: I believe you're saying you want to issue a single SQL statement as follows:

You have the serial # of a device (which does not sound like it's the primary key), and the device data
If the device is disabled, the data should not be inserted into your table.
If the device is enabled, it should be inserted.

Using Stefan's dbfiddle as a starting point, I came up with this:

insert into datain (device_id, datain)
select device_id, @device_data
from device
where serialno = @serial_no
  and state <> 0 ;

My version of the dbfiddle is here; it shows four insert attempts, two for enabled devices, with one for a disabled device and one for an invalid serial # in between. Only the data for the enabled devices that exist is added.

Quick explanation of how this works: You're starting with two know values, the serial number of a device (which uniquely identifies it), and the data that should (maybe) be inserted. We're simply selecting the device_id from the device table that matches your serial number, and is enabled. If the device matching your serial number is disabled, we'll get no rows back in the SELECT, and nothing will be inserted; we only insert the data if we find the serial number, and the device is enabled.

FYI: If you had a table with the serial numbers and device data, you could insert all the valid ones at once with:

insert into datain (device_id, datain)
select dev.device_id, data.device_data
from device dev
       INNER JOIN inbound_data data ON (dev.serialno = data.serial_no)
where dev.state <> 0 ;

Related Solutions

Mysql – How to preserve ID generated from a PRIMARY KEY when moving data into a new table

When you have a Primary Key with an auto_increment it will generate a new ID only if you insert a NULL value. If you set ID=4 in your INSERT, the ID will be 4 so you'll not loose your ID during your "move" operation.

We don't have the "SEQUENCE" notion like in Oracle database so your "global ID" problem it's not so easy to do.

Maybe you can try something like this (but it'll add complications for just a 4 millions rows table)

Create a table used for generates your "Global ID", with one int filed auto_incremented:

CREATE TABLE test.sequence_table (next_id int primary key auto_increment);

When you want insert a new row in your child table:

Solution 1: With SELECT in information_schema

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=(auto_increment - 1) FROM information_schema.tables WHERE table_schema="test" AND table_name="sequence_table"; -- Here I use a MySQL Variable but you can store it in PHP or whatever

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)

Edit after ypercube comment:

Solution 2: With LAST_INSERT_ID()

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=LAST_INSERT_ID(); -- Use of the MySQL function LAST_INSERT_ID()

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)

Mysql – How to insert UNIQUE IDs values from several tables into events table

You can use this insert statement to insert some totally useless data in there (assuming the FK accuracy is not important here) but it satisfies your conditions.

    DELIMITER $$
DROP PROCEDURE IF EXISTS Events_insertion$$
CREATE PROCEDURE Events_insertion()
BEGIN
    DECLARE i  INT;
    DECLARE dTime DATETIME;
    SET i = 1;
    SET dTime = NOW();
    WHILE i  <= 10000 DO
       INSERT INTO EVENTS_TEST VALUES
        (
        null,
        (ELT(0.5 + RAND() * 6, "rep_login",
            "rep_share",
            "email_viewed",
            "brand_page_opened",
            "media_viewed",
            "rep_contacted")),
        LAST_INSERT_ID()+2,
        LAST_INSERT_ID()+3,
        LAST_INSERT_ID()+4,
        LAST_INSERT_ID()+5,
        LAST_INSERT_ID()+6,
        (dTime + interval (LAST_INSERT_ID()) second),
        (dTime + interval (LAST_INSERT_ID()) minute)
        );
       SET  i = i + 1; 
    END WHILE;
END$$
DELIMITER ;

You can call this procedure like this

call Events_insertion();

and also try not to use name events as your table name since it is a reserved word. Hope this could help you :)

Best Answer

Related Solutions

Mysql – How to preserve ID generated from a PRIMARY KEY when moving data into a new table

Mysql – How to insert UNIQUE IDs values from several tables into events table

Related Question