MySQL Forums :: Newbie :: NULL but TRUE


Advanced Search

NULL but TRUE
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


Subject Written By Posted
NULL but TRUE Jonathon Wisnoski 03/28/2013 04:09PM
Re: NULL but TRUE Peter Brawley 03/28/2013 09:44PM
Re: NULL but TRUE Rick James 03/28/2013 11:47PM
Re: NULL but TRUE Peter Brawley 03/29/2013 09:52AM
Re: NULL but TRUE Jonathon Wisnoski 03/29/2013 10:22AM
Re: NULL but TRUE Peter Brawley 03/29/2013 10:39AM
Re: NULL but TRUE Jonathon Wisnoski 03/29/2013 01:03PM
Re: NULL but TRUE Peter Brawley 03/29/2013 01:51PM
Re: NULL but TRUE Jonathon Wisnoski 03/29/2013 02:08PM
Re: NULL but TRUE Peter Brawley 03/29/2013 02:23PM
Re: NULL but TRUE Jonathon Wisnoski 03/29/2013 03:13PM
Re: NULL but TRUE Peter Brawley 03/29/2013 03:58PM
Re: NULL but TRUE Jonathon Wisnoski 03/29/2013 04:01PM
Re: NULL but TRUE Peter Brawley 03/29/2013 04:02PM
Re: NULL but TRUE Rick James 03/30/2013 12:47PM
Re: NULL but TRUE Jonathon Wisnoski 03/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.