Insert new SQL row only if there is same value from other table

Question

Currently I'm having,

Table1:

ID  |   DEVICEID    |  STATUS  |
--------------------------------
1   |   SN001       |  ENROLL  |
2   |   SN002       |  ENROLL  |

Table2:

ID  |   DEVICEID    |  STATUS  |
--------------------------------
1   |   SN001       |  ENROLL  |
2   |   SN002       |  ENROLL  |
3   |   SN003       |  ENROLL  |
4   |   SN004       |  ENROLL  |

There is a form to fill up in order to insert new DEVICEID and STATUS in Table1 using AJAX POST to PHP. Before update the SQL, how do I create a condition where Table1 only can be updated if only there is same DEVICEID filled up in form with DEVICEID in Table2 else it will return error and will not update the Table1.

Here's what I have:

"SELECT deviceid
FROM table1
INNER JOIN table2 ON table1.deviceid=table2.deviceid
INSERT INTO table1(deviceId,status)
VALUES('$deviceid', '$status')
WHERE deviceid = deviceid";                 <--- Not sure what I'm doing here, trying to compare

`mysql` or `sql-server`? – Evaldas Buinauskas Nov 11 '17 at 14:17 — Evaldas Buinauskas, Nov 11 '17 at 14:17

Sonikas · Answer 1 · 2017-11-11T12:58:18.250

0

INSERT INTO table1
SELECT DEVICEID, STATUS
FROM Table2
WHERE DEVICEIUD = @param1 AND STATUS = @param2
LIMIT 1

SELECT ROW_COUNT();

And check number of row effected

edited Nov 11 '17 at 12:58

answered Nov 11 '17 at 12:52

Sonikas

131
1
1
11

AXAI · Answer 2 · 2017-11-11T13:48:29.223

I usually go with two queries

"SELECT EXISTS (
  SELECT deviceid
  FROM table1
  INNER JOIN table2 ON table1.deviceid=table2.deviceid
)"

then if you're using PDO check if the rowCount is == 0 like this

if ($stmt->rowCount == 0) {
    "INSERT INTO table1(deviceId,status)
     VALUES('$deviceid', '$status')
     WHERE deviceid = deviceid
    ";
} else {
    echo "Device ID already exists.";
}

keep in mind, using SELECT EXISTS () will only return 0 or 1, So you won't be able to get any data, If you want to return some data you can go with

SELECT deviceid
FROM table1
INNER JOIN table2 ON table1.deviceid=table2.deviceid
LIMIT 1

Also if you was going to get the data while using PDO Then use rowCount() instead of fetchColumn()

score 0 · Accepted Answer · answered Nov 13 '17 at 12:15

Solved by this:

INSERT INTO table1 (deviceid, status)
SELECT DISTINCT '$deviceid','$status'
FROM table2
WHERE deviceid='$deviceid' AND status='$status'

Thanks to: Insert Into table If similar value exists in another existing table (not a foreign key)

And everyone who helped me. :)

Insert new SQL row only if there is same value from other table

3 Answers3