1

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.

3 Answers 3

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 ;
3
  • Thanks for the assumption, yes sensors (pk) is the PRIMARY KEY and will only return one row. I have tried this in the terminal and it works as I would expect. Will do some more testing in my PHP and update here.
    – gjt211
    Commented Aug 7, 2017 at 9:10
  • Fantastic, this works very well. Now that this is operational, I want to ask a similar question, should I do that here, or in a new question?
    – gjt211
    Commented Aug 7, 2017 at 9:55
  • It's better to post a new one. Add a link to this question, if they are related. Commented Aug 7, 2017 at 10:00
1

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

1

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 ...;
2
  • 1
    SELECT .. WHERE .. is not valid syntax in MySQL. Commented Aug 6, 2017 at 16:59
  • @ypercubeᵀᴹ updated. Commented Aug 6, 2017 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.