duplicating a row in a DB

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

duplicating a row in a DB

Post by pelegk2 »

is there a way to duplicate a row in a DB?
thnaks in advance
peleg
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

My first thought is, if you already have data stored in a row, you wouldn't need to copy it. Duplicate rows could possibly mess up some business logic somewhere.

Are you sure you want to do that?
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

Although I truly agree with McGruff that this is a waste of space, an integrity, logic and conflict risk, here is an example:

Code: Select all

test=> select * from holyday;  idholyday |   who    | from_date  |  to_date   | fd_hd
-----------+----------+------------+------------+-------
         1 | william  | 2004-12-30 | 2004-12-30 | pm
         2 | william  | 2004-12-31 | 2004-12-31 | am
         3 | patricia | 2004-12-31 | 2004-12-31 | am
         4 | caroline | 2004-12-31 | 2004-12-31 | fd
         5 | john     | 2004-12-31 | 2004-12-31 | fd
         6 | patricia | 2004-12-31 | 2004-12-31 | am
         7 | william  | 2004-12-31 | 2004-12-31 | am
(7 rows)
                                                                                                                                             
test=> insert into holyday values((select max(idholyday)+1 from holyday), (select who from holyday where idholyday = 2), (select from_date from holyday where idholyday = 2), (select to_date from holyday where idholyday = 2), (select fd_hd from holyday where idholyday = 2));
INSERT 17224 1
teste=> select * from holyday;  idholyday |   who    | from_date  |  to_date   | fd_hd
-----------+----------+------------+------------+-------
         1 | william  | 2004-12-30 | 2004-12-30 | pm
         2 | william  | 2004-12-31 | 2004-12-31 | am
         3 | patricia | 2004-12-31 | 2004-12-31 | am
         4 | caroline | 2004-12-31 | 2004-12-31 | fd
         5 | john     | 2004-12-31 | 2004-12-31 | fd
         6 | patricia | 2004-12-31 | 2004-12-31 | am
         7 | william  | 2004-12-31 | 2004-12-31 | am
         8 | william  | 2004-12-31 | 2004-12-31 | am
(8 rows)
                                                                                                                                             
test=>
I'm not a database expert but this query does the trick. Btw I'm using PostgreSQL.

SCOrphus.
Post Reply