1

I have a property table where it updates from an XML feed regulary. If the property is not in the database it inserts; if it is there it updates it. But also users can update the fields via a form on the back end of the website. If a user updates a field, it flags it up and changes the status to 1.

My table looks like this:

heading    | nice house  
up_heading |    1  
price      |  40000  
up_price   |    0

In this case, when daily cron job runs the update, it will not update the heading as the up_heading's status is 1, but it will update the price as the status of up_price is 0.

So my question is, how can I write an efficient update query like:

update property
set heading = $heading
case up_heading = 0, set price = $price
case up_price   = 0,

and so on till where p_id = %p_id?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Display name
  • 420
  • 5
  • 20
  • This is what you're looking for: http://stackoverflow.com/questions/17265522/conditional-update-in-mysql – dchayka May 04 '16 at 16:04
  • 1
    What I would do is use PHP in the cron job script to parse the fields and set necessary data, then run a simple UPDATE sql statement with a WHERE clause and avoid conditions in the query. – dchayka May 04 '16 at 16:06
  • `set heading = $heading` < that is missing a comma btw, should that be part of your real code. – Funk Forty Niner May 04 '16 at 16:06
  • this is my code at the moment if($up_p_advert_heading == 0){$update_p_advert_heading = "p_advert_heading = '$p_advert_heading',";} else{ $update_p_advert_heading = "";} if($up_p_postcode == 0){$update_p_postcode = "p_postcode = '$p_postcode', p_lon = '$p_lng', p_lat = '$p_lat', ";} else{$update_p_postcode = "";} if($up_p_price == 0){$update_p_price = "p_price = '$p_price', p_price_numeric = '$p_price_numeric',";} else{$update_p_price = "";} – Display name May 04 '16 at 16:07
  • 1
    please don't dump code in comments, update your question http://stackoverflow.com/posts/37032762/edit – Funk Forty Niner May 04 '16 at 16:08

1 Answers1

1
update property set
    heading = case when up_heading = 0 then $heading else heading end,
    price   = case when up_price   = 0 then $price   else price end
    -- and so on
where p_id = %p_id

Documentation

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53