On Fri, 11 Feb 2005 08:10:25 -0500, Robert <[EMAIL PROTECTED]> wrote: > Let me see if I can explain it better today. :-)
See: http://www.catb.org/~esr/faqs/smart-questions.html > I have about 200 users in "district 1". I need to add each of those users > into "district 2" using the same information that they have in "district 1". > I also need to make sure they are only inserted 1 time. They have a unique > employeeID, so I know I can use that to make sure that they are inserted 1 > time. You do have a unique constraint that would prevent duplicates from being entered, don't you? So, if you try to insert a duplicate entry, the DBMS will reject the second entry. Since you seem to need to repeat the data, you should probably revisit your database design - you probably need a list of users and their information, a list of districts, and a mapping table which says which users correspond to which districts. Unless a user's information for district 2 can be altered independently of their information for district 1 after the initial load, in which case you need a list of users (primary key user ID), a list of districts (primary key district), and a table which lists the user ID, the district, and the information for that user in that district (primary key user ID and district; foreign keys on user ID and district). Of course, this might be what you already have, but since you haven't told us anything significant about your database structure, you get suggestions that may be irrelevant because we can't tell what might be relevant. In Informix terms, it sounds like what you need to do is extract the list of users for 'district 1' into a temp table, mangling the information in the temp table so it refers to 'district 2' instead, and then copying the data from the temp table back into the original. In Oracle, you might be able to do things differently - for example, you may be able to do: INSERT INTO TargetTable SELECT 'district 2 as district, userid, a, b, c, ..., y, z FROM TargetTable WHERE district = 'district 1'; (Informix does not permit you to reference the target table in the SELECT statement, hence the need to use a temp table intermediary.) Of course, this version does not handle possible duplicates - if you think that's a real problem, then you have to do a more complex filtering process on the 'source' table. INSERT INTO TargetTable SELECT 'district 2 as district, userid, a, b, c, ..., y, z FROM TargetTable WHERE district = 'district 1' AND userid NOT IN (SELECT userid FROM TargetTable WHERE district = 'district 2'); If none of this jogs your thought processes, then you need to think in terms of getting a teddy bear to sit beside your monitor, and then you explain to the teddy the process that you need to go through to get the job done in terms the teddy can understand. Then, if you still need some help, you can write it up in an email. It should then be clear whether you need to ask the question in comp.databases.oracle.server news group (because your problem is with Oracle SQL) or here (because your problem is with how to code known SQL in Perl + DBI + DBD::Oracle). (The teddy is not wholly facetious; you can use a penguin, rubber duck, brick wall or colleague instead. The point of the exercise is to get you to articulate the problem clearly. Half the trouble is you have not yet explained to us what it is you want. You're assuming everybody knows what a district is, for example - we know what the term means in general, but not the significance within your database. See Chapter 3 of 'The Pragmatic Programmer' by Andrew Hunt and David Thomas for more information - they call it Rubber Ducking because they suggest using a rubber duck instead of using a teddy bear. ISTR that this was discussed in 'The Psychology of Programming' too, but couldn't immediately find it - though I did find a discussion of how a university lab removed the vending machines outside the student counsellor's area, and the counsellors ended up overworked and very bored because they were dealing with trivia - previously solved by the students chatting around the vending machines.) > "Ron Reidy" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > What do you mean by 'insert that user into a new district'? Do you mean > create an oracle account? > > -- > Ron Reidt > Lead DBA > Array BioPharma, Inc. > > -----Original Message----- > From: Robert [mailto:[EMAIL PROTECTED] > Sent: Thu 2/10/2005 11:26 AM > To: [email protected] > Subject: finding and inserting > I am not sure how to go about this. > > I have an Oracle database with users in a district. Every time I have to add > a new district I have to reset the password. I thought, now why don't I use > Perl to just copy the users current info and insert that into the new > district with the name of the district changing. > > What I need to do is loop through the database and find a user, insert that > user into the new district, find next user, make sure that user is not > already in the new district to prevent duplicates, insert user into new > district changing the district information. There will be exceptions but I > think I can handle those if I can get started on the first part. > > My Perl and DBI skills are not quite up to this...so help would be > appreciated. -- Jonathan Leffler <[EMAIL PROTECTED]> #include <disclaimer.h> Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org "I don't suffer from insanity - I enjoy every minute of it."
