Beta

Slashdot: News for Nerds

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Silly SQL trick

Qzukk (229616) writes | about a year and a half ago

User Journal 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.

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.

cancel ×

2 comments

Kind of like COALESCE() (1)

smitty_one_each (243267) | about a year and a half ago | (#42556573)

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

Re:Kind of like COALESCE() (1)

Qzukk (229616) | about a year and a half ago | (#42556743)

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.

Check for New Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Create a Slashdot Account

Loading...