Re: database setup

MySQL has a nice idiom for this, assuming that the "memberName" field in your "contest.forum" table has a uniqueness constraint (i.e. it is the "PRIMARY KEY" field, or it has "CONSTRAINT UNIQUE" attached to it), and this is enforced by the DB engine (I think this might only work for InnoDB type tables):

INSERT into forum (memberName,original) values (?,?)
  on duplicate key update forum set current=? where memberName=?
[download]

(not tested -- look around for more info about the "on duplicate key update" syntax, and try it out).

So, I guess this isn't really a perl question after all, but if you wanted a perl solution instead (e.g. if the DB engine you're using appears not to enforce the uniqueness constraint), the idea would be to do a select first on the "contest" table for the given memberName value; if that returns a non-empty value, update the "current" field for that row; otherwise, insert a new row.

(If the process involves doing everyone in a single pass, it would be better to pull all the existing names from the "contest" db and hold those in a hash, then do updates or inserts for the new information, depending on whether a given name exists in the hash.)

update: better yet, do as ysth says below.

Comment on Re: database setup Download Code

Replies are listed 'Best First'.
Re^2: database setup by ysth (Canon) on Jun 04, 2007 at 08:39 UTC
`INSERT into forum (memberName,original) values (?,?) on duplicate key update forum set current=? where memberName=?` [download] Assuming you want current set even the first time, do it with: `INSERT into forum (memberName,original,current) values (?,?,?) on duplicate key update forum set current=values(current) where memb +erName=?` [download] if you wanted a perl solution instead (e.g. if the DB engine you're using appears not to enforce the uniqueness constraint), the idea would be to do a select first on the "contest" table for the given memberName value; if that returns a non-empty value, update the "current" field for that row; otherwise, insert a new row. The select is redundant; try the update, and if it processes no rows, try the insert. Better yet, if the database engine you're using doesn't enforce a uniqueness constraint, get a better database system.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^2: database setup
by ysth (Canon) on Jun 04, 2007 at 08:39 UTC

INSERT into forum (memberName,original) values (?,?) on duplicate key update forum set current=? where memberName=?
[download]

INSERT into forum (memberName,original,current) values (?,?,?)
  on duplicate key update forum set current=values(current) where memb
+erName=?
[download]

if you wanted a perl solution instead (e.g. if the DB engine you're using appears not to enforce the uniqueness constraint), the idea would be to do a select first on the "contest" table for the given memberName value; if that returns a non-empty value, update the "current" field for that row; otherwise, insert a new row.

Better yet, if the database engine you're using doesn't enforce a uniqueness constraint, get a better database system.

[reply]
[d/l]
[select]