Slashdot is powered by your submissions, so send in your scoop


Forgot your password?

Slashdot videos: Now with more Slashdot!

  • View

  • Discuss

  • Share

We've improved Slashdot's video section; now you can view our video interviews, product close-ups and site visits with all the usual Slashdot options to comment, share, etc. No more walled garden! It's a work in progress -- we hope you'll check it out (Learn more about the recent updates).

User Journal

Qzukk's Journal: Silly SQL trick 2

Journal by Qzukk

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.

The next person to mention spaghetti stacks to me is going to have his head knocked off. -- Bill Conrad