Thursday, June 26, 2008


The UPDATE...FROM SQL statement saved me tonnes of time during a previous system migration. Using the FROM clause in an UPDATE statement allows you to pull data from one or more tables into the data you're updating.

Say you have a table with 10,000 user records (let's call it tbl_migration_1) which you'd like to find the last login time which is stored in the master user table (let's call it tbl_user). Here's what you do:
update tbl_migration_1
set tbl_migration_1.last_login = tbl_user.last_login
from tbl_user
where tbl_migration_1.user_id = tbl_user.user_id
You can freely add more conditions to match the records.

EDIT: Oops, forgot to mention that I'm using Sybase for this. Not sure if it's ANSI standard. Well, do let me know :)

No comments: