Using delete multiple time as task

Hi,
I’m wondering if I get myself into trouble using delete as regular task.
The flow:
catch the data
beginTransation
delete
insert
if all ok
commit
else
rollback

Thanks in advance.

may be can be a better approach
The flow:
catch the data
beginTransation
delete
insert
if all ok
commit
OPTIMIZE TABLE mytable
else
rollback

the task will be running once a day

why delete followed by insert?

can you use INSERT… ON DUPLICATE KEY UPDATE?

Good point thanks just a lot :slight_smile:
but what I 've to put in the update clause
all in all I’ve no data to update after the insert
I mean I can’t do a thing like this


INSERT INTO `mytable` (
`id` ,
`name`
)
VALUES (
'2', 'test1'
) ON DUPLICATE KEY UPDATE;

so I ended up with an ugly


INSERT INTO `mytable` (
`id` ,
`name`
)
VALUES (
'2', 'test1'
) ON DUPLICATE KEY UPDATE id=id;

the example you showed doesn’t make sense

it should be like this –

INSERT 
  INTO mytable 
     ( id 
     , name )
VALUES 
     ( 2
     , 'test1' )
ON DUPLICATE KEY 
UPDATE [COLOR="Blue"]name = 'test1'[/COLOR]

think about it for a second… it has to be the id value that is the same as the id value of an existing row (assuming id is a key)

therefore you must update the name to the new value

Thanks I got it :slight_smile:
but if the table has got more fields I 've to set a thing like this


INSERT 
  INTO mytable 
     ( id 
     , field1,field2,field3 )
VALUES 
     ( 2
     , 'test1','test2','test3' )
ON DUPLICATE KEY 
UPDATE field1 = 'test1',field2 = 'test2',field3 = 'test3'

and
the same way it will work with table with multiple pk ?

yes, and yes

why don’t you test it yourself?

I just tested but I like to be reassured :slight_smile:

things will go a lot better for everyone if you test first, and ask for reassurance only when the test doesn’t give the results you expect

:slight_smile: