On 5 Apr 2012, at 5:33pm, Pete <p...@mollysrevenge.com> wrote: > Thank you Igor and Simon, I think I understand this now. On reading the > docs more closely, it looks like the scond test case (NOT NULL with a > DEFAULT) could be solved by using INSERT OR REPLACE. I'm wondering if > there might be a way to solve the first test case by using a trigger?
I don't understand what you think is wrong with insert into t3 SELECT Col1 FROM t2; You obviously don't the second column from t2, so don't SELECT it. If you have some kind of logic which states NULL values are perfectly legal in t2. But NULL values are not legal in t3, where they should always be replaced with 'abc', but all non-NULL values must be preserved. Then you could do all your INSERTs, then follow up with a UPDATE t3 SET Col2='abc' WHERE Col2 IS NULL (which will go faster if Col2 is indexed) or you can use SQLite's coalesce(X,Y,...) function as documented in <http://www.sqlite.org/lang_corefunc.html#coalesce> something like insert into t3 SELECT Col1,coalesce(Col2,'abc') FROM t2; Which you do depends on how many NULLs you have, how easy it is to make an appropraite index, and how many rows there are in the table overall. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
