Search This Blog

Tuesday, December 28, 2010

Select first n rows from oracle, MySql and MS Sql

Assume we have table customer with columns id and age. Find first n customers with youngest ages

select * from (select * from customer order by age) where rownum < :n
select * from customer order by age limit :n
MS Sql
select top :n * from customer order by age

Monday, December 27, 2010

Oracle merge into with only one table

I recently wanted to use oracle MERGE INTO to update a table. With MERGE INTO, it usually works with 2 tables, a source and a target table. However, in my case, I only have one table: update the table if the given id exists, otherwise insert a new row. I initially thought the following should work:
MERGE INTO image i
USING (select id, url from offer_image
WHERE id = :id and url = :url) ii
ON ( = AND ii.url = i.url)
title = :title
INSERT (, i.url, i.title)
VALUES (:id, :url, :title)
However, it only does update and not insert. I consulted oracle DBA and here is his answer:
"Your source always needs to return a non-empty for the merge to work. In this case your source is returning 0 rows."
Then I came up the following merge statement:
MERGE INTO image i
USING (select 1 from dual) ii
ON ( = :id AND i.url = :url)
title = :title
INSERT (, i.url, i.title)
VALUES (:id, :url, :title)
Note that Oracle table DUAL is a special one row one column table. Here is a good explanation about DUAL

Thursday, December 23, 2010

Git cheat sheet

I have been using Git for more than a year. Comparing with SVN, the big difference is how to commit the changes to remote repository. Git requires two steps to do it:
  1. commit the changes to local repository (need to add new files explicitly)
  2. push the commit to remote repository
When I first used Git, I often forgot to add new files (git add new_file_name) to let Git track new files, and after commit the changes, I didn't push my changes to remote server.

Here are some commands I would like to share:

Create a new local branch from an existing branch and switch to the new branch:
git checkout -b new_branch existing_branch
Push a newly created branch
git push -u origin new_branch_name
-u will make your branch tracked

Push a tag
         git push origin tag_name

Push all tags not in remote server
        git push origin --tags

Delete a local branch
git branch -D branch_name
Delete a remote branch
git push origin :remote_branch_name
Delete a local tag
git tag -d tag_name
Delete a remote tag
git push origin :refs/tags/tag_name
Remove the references to the deleted remote branches
git remote prune origin
Delete last commit (not pushed yet)
git reset --hard HEAD~1
List all key/value pairs for git config
git config -l
Get a value for a key in git config (for example, get git repository url):
git config --get remote.origin.url
Show all file names changed in a single commit:
git show --pretty="format:" --name-only commit_hash
Cherry Pick
git cherry-pick commit-id-from-other-branch