Journal Qzukk's Journal: Silly SQL trick 2
Tonight's task is to manually categorize a rather flat tree structure in SQL (currently two levels only) by reading the top level entry and assigning it and its children to a category. On a lark I gave this a shot:
update tree set category=1 where 5 in (id,parent);
and it worked in PostgreSQL.
Why? Because I'm sitting here reading the list of top-level entries in one window while using psql in the other and pressing up to edit the previous query. Before I tried that I had
update tree set category=1 where id=4 or parent=4
so I'd have to retype TWO numbers instead of just one.
Kind of like COALESCE() (Score:2)
Re: (Score:2)
Sort of. The x IN (a,b,c,...) operator is a shortcut for (x = a OR x = b OR x=c ...) so if it doesn't match the first one it tries the second one etc. It's also used for subqueries with a single column: x IN (SELECT y FROM foo) which generally translates into an EXISTS clause by adding y=x to the subquery.
Normally when I use it, the a,b,c are either literals or a subquery. This is the first time I tried using columns on the right hand side.