[SOLVED] DOUBLE WHERE

[jaymc]

I have this query

 

UPDATE topic_views

SET views = views + 1, timestamp = '$time'

WHERE topic = '$topic' AND username = '$User'

LIMIT 1

 

That works fine, however there is another field I need to add, and I only want it to update that field if a condition is met. the other SET stuff should always complete. Basically something like this, but correct syntax

 

UPDATE topic_views

SET views = views + 1, timestamp = '$time', (last_id = '$latestID' WHERE last_id < 'oldID')

WHERE topic = '$topic' AND username = '$User'

LIMIT 1

 

Here is the part extracted

(last_id = '$latestID' WHERE last_id < 'oldID')

 

If that condition is met, it updates the last_id field and does the UPDATE on views and timestamp

If the condiotion is not met, it just updates views and timestamp, NOT last_id

 

Can this be done in one query? If so, how

[fenway]

Sure, you can use an IF().

[jaymc]

Sure, you can use an IF().

Can you post an example

 

I have search through google but not getting many examples as its picking up IF as a word in peoples posts on other forums rather than IF as MYSQL

[jaymc]

Its ok, I found it

[jaymc]

Although Im not able to add update multiple fields in the IF statement

 

last_post_read = IF((last_post_read < '50', '4', last_post_read) AND '2',last_post_position)

 

That wont work, at a guess

[roopurt18]

I'm guessing that last_id is a user_id of the person who viewed the topic.  I'm curious why you wouldn't always want to update it to the current value?

[jaymc]

If they view a topic, it will always update the last view time

 

Also in that table is a field which shows last post read

 

I needed to check if the last post read was lower than the current post reading

 

I got it working in the end but I had to use two IF statements in the query as a fudge, I guess thats not the correct way

 

last_post_read = IF((last_post_read < '50', '4', last_post_read),

last_post_id = IF(last_post_read < '50', '4', last_post_id)

 

Ignore the values 50 and 4, in reality they are php vars

 

But yeh, thats how I did it, how can I turn that into a single if and add a multi field update inside the IF