Insert into table where boolean value in another table is true

All we need is an easy explanation of the problem, so here it is.

I want to insert a value into a table, but only if a boolean value in another table is true.

I currently do this using two separate SQL commands, testing the boolean value in PHP and then deciding to insert the value or not in the other table.
Is it possible to do this in a single SQL statement which should be much quicker than my two statement process. (In the example code below, I have hard coded my values, but they come from php variables in the actual code).

SELECT enabled FROM mydb.sensors WHERE pk=10;

In PHP I run the above query, then I test if enabled is true. If true, then I store the sensor data value into another table.

INSERT INTO mydb.sensordata(sensorkey,sensorvalue) values (10, 25.4);

What I am after is something like the following, but not sure how to implement it.

INSERT INTO mydb.sensordata(sensorkey,sensorvalue) values (10, 25.4) WHERE mydb.sensors.pk = 10 AND mydb.sensors.enabled = TRUE;

Any guidance is appreciated.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Assuming that sensors (pk) is the PRIMARY KEY so the query with WHERE pk = ? will not return more than one row, you can use this:

INSERT INTO mydb.sensordata
  (sensorkey, sensorvalue) 
SELECT s.pk, 25.4 
FROM mydb.sensors AS s
WHERE s.pk = 10 
  AND s.enabled ;

Method 2

One possible solution …

Setup:

create table sensors(pk int, enabled bit);

insert into sensors values (10,1);

create table sensordata(sensorkey int, sensorvalue float);

Insert if enabled is true:

insert into sensordata (sensorkey,sensorvalue)
select 10,25.4 from dual
where exists(select 1 from sensors where pk = 10 and enabled);

NOTE: from dual required as a dummy table for select to function properly.

Results:

select * from sensors;

 pk      enabled
 ------- -------
      10 true

select * from sensordata;

 sensorkey sensorvalue
 --------- -----------
        10        25.4

sqlfiddle

Method 3

You don’t even need the EXISTS test for this. That condition will only return null or one value,

INSERT INTO mydb.sensordata(sensorkey,sensorvalue)
SELECT *
FROM ( SELECT 10, 25.4 ) AS t
WHERE (SELECT enabled FROM mydb.sensors WHERE pk=10);

here is a simplified example with PostgreSQL,

test=# CREATE TABLE foo (bar int);
CREATE TABLE
test=# INSERT INTO foo SELECT 0 WHERE false;
INSERT 0 0
test=# INSERT INTO foo SELECT 0 WHERE null;
INSERT 0 0
test=# INSERT INTO foo SELECT 0 WHERE true;
INSERT 0 1

In MySQL you can’t do SELECT … WHERE, so you have to wrap it in extrasyntax, but the idea is the same.

SELECT *
FROM (SELECT (0)) AS t
WHERE ...;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply