Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!

 



Forgot your password?
typodupeerror
Check out the new SourceForge HTML5 internet speed test! No Flash necessary and runs on all devices. ×
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.

Save energy: Drive a smaller shell.

Working...