Please create an account to participate in the Slashdot moderation system


Forgot your password?
User Journal

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.

This discussion has been archived. No new comments can be posted.

Silly SQL trick

Comments Filter:
  • Only in the WHERE and not the SELECT clause, or am I missing something?
    • by Qzukk ( 229616 )

      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.

"I have not the slightest confidence in 'spiritual manifestations.'" -- Robert G. Ingersoll