CODEWITHSUNDEEP
sql
gosukiwi
gosukiwi

Reputation: 1575

Inserting entries only once

I have a relationships table, the table looks something like this

 ------------------------
| client_id | service_id |
 ------------------------
| 1         | 1          |
| 1         | 2          |
| 1         | 4          |
| 1         | 7          |
| 2         | 1          |
| 2         | 5          |
 ------------------------

I have a list of new permissions I need to add, what I'm doing right now is, for example, if I have to add new permissions for the client with id 1, i do

DELETE FROM myTable WHERE client_id = 1
INSERT INTO ....

Is there a more efficient way I can remove only the ones I won't insert later, and add only the new ones?

Upvotes: 0

Views: 218

Answers (2)

Emir Akaydın
Emir Akaydın

Reputation: 5823

yes, you can do this but in my humble opinion, it's not really sql dependent subject. actually it depends on your language/platform choice. if you use a powerful platform like .NET or Java, there are many database classes like adapters, datasets etc. which are able to take care of things for you like finding the changed parts, updating/inserting/deleting only necessery parts etc.

i prefer using hibernate/nhibernate like libraries. in this case, you don't even need to write sql queries most of the time. just do the things at oop level and synchronize with the database.

Upvotes: 1

JerseyMike
JerseyMike

Reputation: 899

If you put the new permissions into another table, you could do something like:

DELETE FROM myTable WHERE client_id in (SELECT client_id FROM tmpTable);

INSERT INTO myTable AS (SELECT client_id, service_id FROM tmpTable);

You are still taking 2 passes, but you are doing them all at once instead of one at a time.

Upvotes: 0

Related Questions