Qzukk's Journal: Silly SQL trick

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()

[smittyoneeach]

Only in the WHERE and not the SELECT clause, or am I missing something?

Re:

[Qzukk]

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.