I have another technique for this kind of data merge that speeds things up
(a lot!) but it requires a third table to make it work
Please post the results of SHOW CREATE TABLE for both the source data
table and the destination data table and I will show you how it works.
Thanks,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Aleksander V. Dyomin" <[EMAIL PROTECTED]> wrote on 09/29/2004 04:22:02 PM:
> Thanx for reply!
>
> В Срд, 29.09.2004, в 23:21, Jim Grill пишет:
> > > $key='somekeyvalue';
> > > dbquery("update sometable set count=count+1 where keyfield='$key'");
> > > if(mysql_affected_rows()<1)
> > > dbquery('insert into sometable set keyfield='$key', count=1');
> > > ---
> > >
> > > First question: this is good method? Or I MUST use pre-query with
> > > "select count(...) from sometable where keyfield='$key'" for
> detection:
> > > exists needle record(for update) or not(for insert)???
> >
> > This method is fine. That is to say that I have seen it before in
> older
> > code. However, you might take alook at REPLACE:
> > http://dev.mysql.com/doc/mysql/en/REPLACE.html
> If I understended this command right - its not for me.
> There is I cant set different values for different cases(record exists
> and not)...
>
> > And for MySQL 4.1.x you might take a look at INSERT ... ON DUPLICATE
> KEY
> > UPDATE
> > http://dev.mysql.com/doc/mysql/en/INSERT.html
> Not for me too, becouse my script MUST work fine on 3.x too.
>
> > > Second... My script work on many different hosts with different
> > > hardware, os(only Linux or FreeBSD), and different PHP and MySQL
> > > version. It works fine excepting one thing... Sometime happens
> errors
> > > like:
> > > MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert
> into
> > > sometable set keyfield='somekeyvalue', count=1
> >
> > I would look at your logic. What is $key? Where does it come from? If
> > `keyfield` is a unique index then you cannot have duplicate values.
> Yes. Keyfield is UNIQUE. For example we want track url hits in format:
>
> ...someurl.php?link=linkname1
> // insert ... set link='linkname1', count=1
> // or
> // update ... set count=count+1 where link='linkname1'
>
> ...someurl.php?link=linkname2
> // insert ... set link='linkname2', count=1
> // or
> // update ... set count=count+1 where link='linkname2'
> ...etc...
>
> look: for insert count value=1, for update - increment...
> can I use REPLACE in this situation? (without 'select count where
> link='linkname...' :)))
>
> > > Why this happens? On some hosts this messageis very rare, but on
> others
> > > - so often... I cant understand reason :(
> > > My script use DB locks, so two clients cant make this situation(i
> > > think).
> >
> > Do a little research to find out if your locking is working.
> its work while I testing, but I not sure that its work at moments when
> my problem is happens... But track this moments is too hard for me,
> becouse its happens not regulary and wihout any stable(visible) reasons
> :(
>
> > > Only one idea: sometime 'update' query dont work(or work, but dont
> > > return good result), and 'insert' query started... But WHY?
> >
> > Again, I would look at your logic and try to use REPLACE or INSERT ...
> ON
> > DUPLICATE KEY UPDATE.
> not for my clients :(
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
