Insert ... on duplicate key update...

Highlighted
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
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!

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download