mysql query - insert new while delete old entries

likwid

likwid

likwid

Associate
Joined
26 Nov 2004
Posts
1,480
Location
Gran Canaria
Hi

Is there a mysql query that will enter new entries into a table while remove those not included in the insert.

For example:

I wish to insert x, y, and z into a table currently containing v, w and x. Can i remove v and w without a separate delete query?

Cheers:)
 
If I understand you correctly, you can use UPDATE?

UPDATE test SET text='new insert here' WHERE text='Here was old result';
 
Thanks for reply.

To update I would need first to query the db to find which of those entries I wish to insert already exist, then use them to form the where part of the query.

Ideally i'd like to insert the entries to the table and it would discard those entries not included in the query.
 
So you're looking to replace the entire contents of the table with the new inserts?

Doing that on DB2 I'd do a load or import with the replace option. Not sure what the equivalent for mysql is.
 
Something like that, except there would be a where condition, in this case a foreign key. So to remove everything with a particular foreign key, and replace with new entries would be great.

Thanks for your advice :)
 
Last edited:
from the small version you've posted, it sounds like you need to truncate the table before you do your import. Do you want to delete ALL your old entries?

As far as I'm aware, MySQL will only do this in two/more queries.
 
Sorry, I didn't do a good job of explaining myself.

Table = 'tokens'

id|token
---------
6|token1
6|token2
6|token3
7|token4
7|token5
7|token6
8|token7
9|token8

delete from tokens where id = '7';
insert into tokens (id, token) values (7, token9),(7, token10),(7, token11);

Is there any way I can skip the first query?

Thanks :)
 
Unless you can guarantee that there will be the same number of deletes as inserts then no, I think you need two separate operations.
 
Won't that on duplicate fail because there isn't a true primary key (at least not in the example), surely it would end up setting all the id=7 rows to token9, then to token10 and finally to token11?
 
Thanks for reply. I can't see on duplicate working as i require in that it would leave existing tokens. The primary key is the token name, not the id which is foreign.
 
Sorry, I didn't do a good job of explaining myself.

Table = 'tokens'

id|token
---------
6|token1
6|token2
6|token3
7|token4
7|token5
7|token6
8|token7
9|token8

delete from tokens where id = '7';
insert into tokens (id, token) values (7, token9),(7, token10),(7, token11);

Is there any way I can skip the first query?

Thanks :)
 
You must log in or register to reply here.
Back
Top Bottom