How to check if a value exists in tableA before inserting in tableB?

Question

I have a condition like this:

0 < $var < (SELECT col FROM tableA ORDER BY col DESC LIMIT 1)

Also I have an INSERT query like this:

INSERT INTO tableB (col) VALUES (?) ON DUPLICATE KEY UPDATE col = VALUE(col)

Now I want to know, how can I implement this in MySQL:

If the above condition was true, then insert new row, otherwise do nothing or give me an error?
```
if (condition == true) then insert into table
```

looks like a fusion of php and mysql. btw you can use max(id). — Drew, Sep 20 '15 at 14:55
ODKU would not have value wrapper. You can use `insert ignore` with no ODKU — Drew, Sep 20 '15 at 14:56
@Drew yea I guessed I should use `MAX()`, but honestly I don't know how — Shafizadeh, Sep 20 '15 at 14:58

Jonathan Leffler · Accepted Answer · 2015-09-20T16:11:20.333

SELECT MAX(col) FROM tableA gives you the maximum value in column col from table tableA.

I think what you're asking to do is:

If $val (a value from somewhere — presumably your PHP) is larger than 0 and smaller than the maximum value stored in the table tableA, then ensure that the value appears in the table tableB.

If so, you might be able to use:

INSERT INTO tableB(col)
    SELECT $val
      FROM dual
     WHERE $val > 0
       AND $val < (SELECT MAX(col) FROM tableA)
       AND NOT EXISTS(SELECT col FROM tableB WHERE col = $val)

You now need to translate that into appropriate PHP.

(See also MySQL conditional insert.)

How can I add ON DUPLICATE KEY to my query?

Also you can remove the AND NOT EXISTS(SELECT col FROM tableB WHERE col = $val) because col is unique and it prevents inserting duplicate.

You don't need the ON DUPLICATE because the NOT EXISTS makes it irrelevant. And if you don't mind the error from an attempted duplicate insertion, you don't need any of this: you just do INSERT INTO tablaB(col) VALUES(?).

AFAICS, the ON DUPLICATE clause does a no-op UPDATE; this avoids the need to do that. I suppose it wouldn't be a no-op if there are triggers on the table such that the 'last modified' (time, or user, or both) columns will be changed, or if the key of the table isn't the col column — these are pretty esoteric possibilities, though. You could have helped by showing an outline schema of the table indicating such features.

If you really need the ON DUPLICATE clause, you can read the manual as well as I can and add it after the SELECT.

INSERT INTO tableB(col)
    SELECT $val
      FROM dual
     WHERE $val > 0
       AND $val < (SELECT MAX(col) FROM tableA)
       AND NOT EXISTS(SELECT col FROM tableB WHERE col = $val)
    ON DUPLICATE KEY UPDATE col = VALUE(col)

(And, of course, if you really don't want the NOT EXISTS clause, you can remove it.)

just one thing: how can I add `ON DUPLICATE KEY` to my query ? — Shafizadeh, Sep 20 '15 at 15:51
also you can remove this `AND NOT EXISTS(SELECT col FROM tableB WHERE col = $val)` because `col` is `unique` and it prevents inserting duplicate. — Shafizadeh, Sep 20 '15 at 15:56
look, one thing is odd for me, why my question has a flag *[unclear what you're asking]* ? because I think my question is clear. do you know why my question has one close vote? — Shafizadeh, Sep 21 '15 at 09:52
Somebody didn't like it. Don't worry about it. AFAIK, in a few days, that flag will expire (unless other expletive weigh in too). — Jonathan Leffler, Sep 21 '15 at 13:57

How to check if a value exists in tableA before inserting in tableB?

1 Answers1