One Star

Insert ... on duplicate key update...

TOS 2.4.0
perl 5.8.8
ubuntu 8.04
mysql 5.0.51
1. I'm trying to go from CSV to MYSQL database.
2. there will be duplicate data but I would like to update columns when a duplicate tries to get inserted.
3. I have set the action on table to insert or update
4. The job runs but it is not updating other columns.
Is this possible with Talend?
Below is what I would like to accomplish.

#mysql script of what I would like to accomplish
#start
mysql of what I would like to do
drop table if exists `test`;
create table test (
id int(10) not null auto_increment,
name varchar(255) not null,
col varchar(255) not null,
update_dt timestamp,
Primary key (id),
unique key (name)
);
insert into test (name, col) values ('a', 1) on duplicate key
update name=name, col=values(col);
insert into test (name, col) values ('a', 2) on duplicate key
update name=name, col=values(col);
insert into test (name, col) values ('a', 3) on duplicate key
update name=name, col=values(col);
#end
#the result should be
SELECT * FROM test;
1,a,3,$date
Tags (1)
6 REPLIES
Not applicable

Re: Insert ... on duplicate key update...

Did you make sure to set the key-column to primary key in your components data schemas?

Regards,
Theo
Employee

Re: Insert ... on duplicate key update...

At the end of the execution, you have
1,a,1
2,a,2
3,a,3

or
1,a,1

Slightly different question than Theo : did you make sure you set the "name" (which is not primary key but unique key) as a Talend schema key?
One Star

Re: Insert ... on duplicate key update...

Theo,
Yes, I did specify it as a primary key.
Plegall,
At the end of execution I have:
1,a,1
I did specify the unique name key as a Talend Schema key.
Employee

Re: Insert ... on duplicate key update...

I don't reproduce your problem. In fact, I reproduce what you expect :-)
I have created the "test" table with you query.
Ubuntu 7.10
Perl 5.8.8
Talend Open Studio 2.4.0
MySQL 5.0.45
Starting job topic3439 at 23:17 10/07/2008.
DBD::mysql::st execute failed: Duplicate entry 'a' for key 2 at /talend/releases/2/24/240/workspace/.Perl/SMART.job_topic3439_0.1.pl line 448.
DBD::mysql::st execute failed: Duplicate entry 'a' for key 2 at /talend/releases/2/24/240/workspace/.Perl/SMART.job_topic3439_0.1.pl line 448.
.---------------------------------------.
| tLogRow_1 |
+----+------+-----+---------------------+
| id | name | col | date |
+----+------+-----+---------------------+
| 1 | a | 3 | 2008-07-10 23:17:05 |
'----+------+-----+---------------------'
Job topic3439 ended at 23:17 10/07/2008.

One Star

Re: Insert ... on duplicate key update...

Ok, I have seemed to resolve it. It was caused by another column "id" that was the primary key.
thanks
One Star

Re: Insert ... on duplicate key update...

Hi, sorry for raising an old topic, but could you please explain how "on duplicate key update" functionality was implemented here? How should it be done in case of inserting to the table with auto increment id?
Right now I see that the only solution is to use tMysqlRow component.
Thanks a lot!