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 »

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 »

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 »

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 »

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 »