MySQL Forums
Forum List  »  Newbie

Posted by: Jonathon Wisnoski
Date: March 28, 2013 04:09PM

I have a feeling that this is the wrong section, but it seemed to be the only one general enough (and I am quite the newbie).

I was wondering if there has ever been a discussion about adding a value similar to Perl's "0 but true", specifically "NULL but TRUE". A value that would evaluate to true in a MySQL conditional statement, but act like NULL otherwise.

For example: `UPDATE table SET col=? WHERE id=? AND col<>?`
This seems like a perfect way to update a row, only if that update would accomplish something (probably the most optimal way to update a row and figure out if you actually changed anything by checking how many rows where affected).

But if "col" is NULL then col<>? returns NULL and that apparently gets cast to FALSE.

The solution is rather long winded and hackish, IMHO: `UPDATE table SET row=? WHERE id=? AND (col IS NULL OR col<>?)`, which only gets worse and worse the more rows you are checking.

Edited 1 time(s). Last edit at 03/29/2013 05:21AM by Jonathon Wisnoski.

Options: ReplyQuote

Written By
March 28, 2013 04:09PM
March 28, 2013 09:44PM
March 28, 2013 11:47PM
March 29, 2013 09:52AM
March 29, 2013 10:22AM
March 29, 2013 10:39AM
March 29, 2013 01:03PM
March 29, 2013 01:51PM
March 29, 2013 02:08PM
March 29, 2013 02:23PM
March 29, 2013 03:13PM
March 29, 2013 03:58PM
March 29, 2013 04:01PM
March 29, 2013 04:02PM
March 30, 2013 12:47PM
March 29, 2013 05:26AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.