2 ms·
SQL's got you covered here: UPDATE sometable SET foo = COALESCE(?, foo), bar = COALESCE(?, bar), baz = COALESCE(?, baz), ba
by yellowapple 7y ago
SQL's got you covered here:
UPDATE sometable SET
foo = COALESCE(?, foo),
bar = COALESCE(?, bar),
baz = COALESCE(?, baz),
bam = COALESCE(?, bam),
bat = COALESCE(?, bat)
WHERE id = ?
And then you'd pass in NULLs for any columns not being updated at the moment.
Confirmed that this works exactly as expected in SQLite:
$ sqlite3 optional_column_test.db
sqlite> CREATE TABLE sometable (id INTEGER PRIMARY KEY, foo, bar, baz, bam, bat);
sqlite> INSERT INTO sometable (foo,bar,baz,bam,bat) VALUES ('You', 'are', 'a', 'bold', 'one');
sqlite> SELECT * FROM sometable;
1|You|are|a|bold|one
sqlite> UPDATE sometable SET
...> foo = COALESCE(NULL, foo),
...> bar = COALESCE('ain''t', bar),
...> baz = COALESCE(NULL, baz),
...> bam = COALESCE(NULL, bam),
...> bat = COALESCE(NULL, bat)
...> WHERE id = 1;
sqlite> SELECT * FROM sometable;
1|You|ain't|a|bold|one
- kelnos 7y agoI'd never heard of COALESCE before! I'm very very far from a SQL expert, but I'm surprised that's never popped up for me before. Are there any bad performance implications here? Like, will it still rewrite columns that don't need to be updated?
- yellowapple 7y agoGood question. The answer to that is almost certainly "it depends on your DB". Most query planners should be smart enough to not try to write anything, but some are smarter than others. For example, SQL Server (if I'm understanding right) does perform a log write even if it should be obvious that the value won't change: https://www.sql.kiwi/2010/08/the-impact-of-non-updating-updates.html https://www.sql.kiwi/2010/08/the-impact-of-non-updating-upda... That being said, I'd skeptical of the performance impact (if any) being all that significant; even in a worst-case scenario of "yeah, it's going to stupidly write every field every time", the columns for each row should be pretty close together both in-memory and on-disk, so unless you're writing a whole bunch of giant strings or something at once (and even then) there shouldn't be a whole lot of seeking happening. tl;dr: assuming there's a minor performance impact is reasonable, but it should probably be measured before trying to optimize it away.