How to alter data in one table based on a query from another table

PostgreSQL

Interesting way how to alter data in one table based on a query from another table.

UPDATE member SET status='excellent' FROM job WHERE job.salary > 5000 AND member.job_id = job.job_id;

This query will set member.status to be 'excellent' where ever a member.job_id refers to a job with salary greater than 5000.

For reference:
Table member columns are status and job_id, among others, where job_is is a foreign key to table job.
Table job columns are job_id as primary key and salary as a number.

DB Tricks

I've started reading some very interesting programming books and I found that, even though the content was awesome and I learned a lot more from these books than from years of programming hands-on experience, it was hard to go back and find a particular solution when needed. So I decided to compile my own list of Tricks and Tips so that I can easily find them when I need them the most.

Almost all of the tricks and tips here are extracted from one of the books listed on the left, but I will not specify which tip came from which book. I will also not copy/paste their content but rather create my own descriptions and titles. Furthermore, the tricks and tips in this list are just a tiny subset of everything found in these books. Thus, if you really want to learn something, you should consider purchasing these books at some point and maybe compiling a list of your own. I purchased all of them and I would have been sorry if i didn't.


This Blog is a part of the Tricks Series:

• Java Tricks
• Ajax Tricks
• Web Application Tricks
• DB Tricks
• Interface Design Tricks