Posted in Programming, Query on Aug 14th, 2009
Summary ::
Try to insert the new row into TABLE
While the insertion fails because a duplicate-key error (primary key)
DELETE conflicting row that had duplicate key value from TABLE
Try again to INSERT the new row into the TABLE
Syntac ::
REPLACE INTO F SELECT * FROM T;
REPLACE […]
Read Full Post »
Posted in Programming, Query on Jul 10th, 2009
ms_user table
user_id
username
email
area_id
1
sherlock
c_sherlock@email.com
1
2
poirot
poirot@email.com
2
3
watson
b_watson@email.com
1
4
hasting
hasting@email.com
2
5
mycroft
a_mycroft@email.com
1
SELECT * FROM `ms_user`
ORDER BY `area_id` , `email` DESC , `username`;
Read Full Post »
Posted in Programming, Query on Jun 3rd, 2009
The new value is not inserted if there is a duplicate key.
syntax ::
INSERT ignore into T values(a, b, c)
INSERT ignore into T(a, b, c) SELECT a, b, c FROM F
*TABLE must have a primary key.
Read Full Post »
Posted in Programming, Query on Jun 3rd, 2009
A DISTINCT and GROUP BY usually generate the same result, so performance should be the same across both query constructs.
the diff ::
Use “Group by” when you use aggregate query, and “DISTINCT” when you want to distinct values.
Essentially it is just a matter of syntax in this case, the end result should be same.
summary […]
Read Full Post »
Posted in Programming, Query on May 25th, 2009
PostgreSQL 7.03
Normal
autocommit on
fsync off
insert | update
insert | update
insert | update
5:16 | 6:46
n/a | n/a
0:10 | 0:18
“Insert” is faster than “update”.
Read Full Post »