Insert incoming data (and pk from 2nd table) into 1st table if true boolean value from 2nd table 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;`
Maybe the following SQL code will help you to find a solution.  The comments "-- step1, -- step 2" etc refer to your question. Dbfiddle here , also tested with MySQL 5.5.

create table device (
  serialno int primary key
, dataval int
, state tinyint
);
-- use tinyint instead of bool 0 = false, non-zero = true
-- https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

insert into device (serialno, dataval, state)
values 
 (1,11,1),(2,22,0),(3,33,1),(4,44,0)
,(5,55,1),(6,66,0),(7,77,1),(8,88,0);

create table datain (
  id int auto_increment primary key
, device_serialno int references device(serialno)
, datain int
);


"Steps to complete" ... NOTICE: "step 3" contains both the SELECT and INSERT, as required (as one statement).  Steps 1 and 2 are just for "illustration".

-- step 1
select serialno, state from device;

-- find all devices and states (CASE - just to make the output more "readable")
select serialno
, state
, case 
    when state = 0 then 'disabled'
    else 'enabled'
  end state_
from device;

-- "supplied sensor serial number" 
set @id_ = 5;

-- step 2
select serialno, state 
from device
where serialno = @id_
  and state &lt;&gt; 0 ;

-- step 3
insert into datain (device_serialno, datain)
select serialno, dataval
from device
where serialno = @id_
  and state &lt;&gt; 0 ;


If you wanted to insert data from all enabled devices, you could maybe use something like ...

insert into datain (device_serialno, datain)
select serialno, dataval
from device
where state &lt;&gt; 0 ;