CODEWITHSUNDEEP
mysqlsql
Nate
Nate

Reputation: 28414

How to use WHERE clause in INSERT query with ON DUPLICATE KEY UPDATE?

I've been reading other questions on this topic, but can't seem to get what I'm trying to do to work.

Here's my query:

INSERT INTO entries
(
    num_entries,
    m_d_y,
    last_entry
)
VALUES
(
    1,
    '01_24_2014',
    UNIX_TIMESTAMP()
)
ON DUPLICATE KEY UPDATE num_entries = (num_entries + 1), last_entry = UNIX_TIMESTAMP()

The query works, but I want to add a condition WHERE the UPDATE is only executed IF last_entry is more than one second ago.

I tried adding a WHERE clause, but it didn't work because the INSERT statement doesn't have a WHERE clause.

Is there any way to add some sort of WHERE condition to my query?

The reason I'm trying to do it like this is that in my PHP code I'm checking to see if the query affected any rows. My goal here is to:

  1. Insert a new row if it doesn't exist
  2. Update the row if it does exist AND if the last entry was one second ago
  3. To detect if the row was inserted or updated in PHP by checking to see if any rows were affected (which is why I'm trying to make the query like this)

Upvotes: 1

Views: 514

Answers (2)

Rudie
Rudie

Reputation: 53871

Can't you just change the query in the script language you're using? last_entry comes from that language (or now from UNIX_TIMESTAMP()), so you can add or omit the ON DUPLICATE KEY UPDATE part:

query = "INSERT INTO ...."
if less than a second ago:
  query += "ON DUPLICATE KEY UPDATE ...."

Right?

Or do you mean the existing last_entry has to be > 1 sec old? That might be even easier?:

ON DUPLICATE KEY UPDATE
  num_entries = IF(last_entry-1 < UNIX_TIMESTAMP(), num_entries + 1, num_entries),
  last_entry = IF(last_entry-1 < UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), last_entry)

Or something like that? =)

(This is completely untested.)

Upvotes: 1

websch01ar
websch01ar

Reputation: 2123

Do a INSERT SELECT like this contrived example:

INSERT INTO entries (col names)
Select 1, '01_24_2014', UNIX_TIMESTAMP() WHERE critera
ON DUPLICATE KeY UPDATE ...

MySQL Reference on this is here.

Upvotes: 1

Related Questions