If exists update else insert on basis of WHERE clause

Question

This query is updating DB table by using more than one checks in where. The problem is i want to insert all missing rows against consultant id's in that week and day.

UPDATE agenda
SET comments = 1
WHERE  term_id = 31
  AND day IN ( 1, 3 )
  AND week IN ( 1, 3, 4 )
  AND consultant_id IN ( 1, 2, 3)

For example There is a row having consultant_id=3 and week=1 which has no day=2 in the database, I want to insert new row having consultant_id=3 ,week=1, day=2 and comments=1. In case the row with consultant_id=3 ,week=1, day=2 exist it should simply update comments.

What i tried to use ON DUPLICATE KEY this related ticket But the problem is I have all consultant_id's, week's and day's in an array and I have to update comments on the basis of these three.

Will really appreciate any help.

Post sample data and desired output in a tabular form. It might help to get your answer faster. — peterm, May 30 '13 at 06:25
... easiest (for testing) when provided as `CREATE TABLE ....` and `INSERT INTO ...` statements — VolkerK, May 30 '13 at 06:28

score 0 · Answer 1 · answered May 30 '13 at 06:28

0

Does it have to be in one query?

A solution could be (pseudocode):

$result = query("UPDATE agenda
    SET comments = 1
    WHERE  term_id = 31
       AND day IN ( 1, 3 )
       AND week IN ( 1, 3, 4 )
       AND consultant_id IN ( 1, 2, 3) ");
if (affected_rows($result) == 0) {
  // do insert query
}

answered May 30 '13 at 06:28

Bart Friederichs

33,050
15
95
195

how can i know which rows still remain to insert? – Mansoor Jafar May 30 '13 at 06:30
Well, the ones that weren't in your `WHERE` clause. Hard to say without knowing some background. – Bart Friederichs May 30 '13 at 06:31

score -1 · Answer 2 · answered May 30 '13 at 06:58

-1

I think your query is right, but if the example you gave reflected your table the query will not update anything.

You want update the table with consultant_id=3 ,week=1, day=2 and comments=1.

In your query you put this statement AND day IN ( 1, 3 ) which means you limit the days to be only one and three. So day=2 won't satisfy the condition, which means the update won't work.

If you want to add new record use insert statement instead.

answered May 30 '13 at 06:58

Bere

1,627
2
16
22

Actually, that is my question. How can i handle this situation, If record is not present to update?? – Mansoor Jafar May 30 '13 at 07:06

If exists update else insert on basis of WHERE clause

2 Answers2