[ciapug] mysql 3.x to mysql 4.1

[Dave J. Hala Jr.]

It looks like the solution to this problem is make the "SELECT" portion
of the query a left join.

Something like this:

INSERT INTO table2 (field1,field2) SELECT (field1.table1, field2.table1)
FROM table1 left join table2 ON field1.table1=field1.table2
WHERE table2.field1 IS NULL

:) Dave


On Fri, 2006-08-18 at 09:03, Dave J. Hala Jr. wrote:
> When using mysql 3.26 I had this function that copied records from
> table1 to table2. The query in the fucntion looks like this:
> 
> INSERT INTO table2 (field1,field2) SELECT (field1, field2) FROM table1
> WHERE field1='X'
> 
> In table2, field1 was a primary key.  If I ran the query, and tried to
> insert a duplicate, mysql 3.x would just "skip" that insert and continue
> on.  After switching to mysql 4.x, I discovered that when 4.x encounters
> a duplicate, it stops. This a problem.
> 
> I took a wild guess and tried making field1 a "unique key" and it didn't
> solve the problem. Anyone got a work around for this problem? 
> 
> 
> :) Dave
-- 

Open Source Information Systems, Inc. (OSIS)
Dave J. Hala Jr., President <dave at osis.us>
641.485.1606