On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS) <michael.bla...@ngc.com>wrote:
> And Oracle says the opposite: > > Yet they all give the same answer when done with "update testtable set > testrow=null where testrow not null; > > You keep hammering this one, it is obvious, I understand, THANKS! What if the SET and WHERE contain many columns? Now I have to add a "WHERE column<>mynewval" for every column in SET to get the actual changes, something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE <insert complex where clause> AND col1<>?1 AND col2<>?2 AND col3<>?3. (passing a null parameter to the above won't even work!) > > Connected to: > Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit > Production > With the Partitioning, Oracle Label Security, OLAP, Data Mining, > Oracle Database Vault and Real Application Testing options > SQL> create table testtable(testrow number); > Table created. > SQL> insert into testtable values(NULL); > 1 row created. > SQL> insert into testtable values(NULL); > 1 row created. > SQL> insert into testtable values(NULL); > 1 row created. > SQL> update testtable set testrow=null; > 3 rows updated. > SQL> update testtable set testrow=null; > 3 rows updated. > No surprises there. Oracle has never managed to impress me. > SQL> update testtable set testrow=null where testrow is not null; > > 0 rows updated. > > So rather than holding your breath for Oracle to change I'd recommend you > do it the portable way. > > I'm not waiting for anything. My last question was simple: which is better? Since MySQL does it the "correct way" perhaps we can just think about this for sqlite4? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users