Friday, November 09, 2007

sql snippets

Gave a link to the new php program schedule page from the schedule page 2 days back, so it is in some sort of public beta. Found some bugs, one of which was due to languageid being NULL in our local db. Solution was to make languageid 1 the default value for that field, so that future record inserts will automatically have it set (tested) and also ran
update file_information set languageid=1 where languageid is NULL
Struggled a bit to get the syntax right, with languageid ='', languageid = NULL , NOT (languageid>0 and languageid<9) and so on, till I googled the correct syntax for NULL.

One more snippet of SQL Gyaan is that to insert, update etc for fields with type interval / timestamp , the syntax is
UPDATE "file_information" SET "duration" = interval '00:01:09' where file_id = 2020
and so on. One has to do a sort of typecasting using the keyword interval. One can even say interval '1 hour' and so on. From http://www.linuxjournal.com/article/9618

No comments:

Post a Comment