How to do row insert conditionally, i.e. INSERT INTO with WHERE NOT EXISTS?

Question

Let's consider this table:

    +----------+----------+
    | user_id  | store_id |
    +----------+----------+
    |    39    |    8     |
    |    8     |    39    |
    |    5     |    11    |
    +----------+----------+

I mapped it with slick with (dropping indexes & foreign keys for simplifying it):

case class FavoriteStore(userId: Long, storeId: Long)

class FavoriteStoreTable(tag: Tag) 
     extends Table[FavoriteStore](tag, "FAVORITE_STORE") {
  def userId = column[Long]("USER_ID")
  def storeId = column[Long]("STORE_ID")
  override def * = (userId, storeId) <> 
              (FavoriteStore.tupled, FavoriteStore.unapply)
}

I would like to be able to write the following SQL query with Slick:

INSERT INTO favorite_store (user_id, store_id) VALUES (39, 8)
WHERE NOT EXISTS 
    (SELECT * FROM favorite_store WHERE user_id = 39 AND store_id = 8)

Any idea?

Slick currently does not support WHERE NOT EXISTS. Please open a feature request: https://github.com/slick/slick/issues/new — cvogt, May 08 '14 at 12:33

score 1 · Accepted Answer · answered May 08 '14 at 14:51

1

This is currently not supported by Slick and I created the following issue: https://github.com/slick/slick/issues/800

answered May 08 '14 at 14:51

n1r3

8,593
3
18
19

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

Since at least Slick 3.0.0 this is now possible with composed bind queries in Slick.

Check out this answer on a similar question: https://stackoverflow.com/a/31352126/3305586

More information on composing binded queries (gave me better understanding of what was done in the answer I just passed a link to: Scala slick query where in list

edited May 23 '17 at 12:30

Community

1
1

answered Aug 12 '15 at 12:11

RoyB

3,104
1
16
37

How to do row insert conditionally, i.e. INSERT INTO with WHERE NOT EXISTS?

2 Answers2