INSERT DISTINCT? how do you do that?

Results 1 to 3 of 3

Thread: INSERT DISTINCT? how do you do that?

  1. #1
    Tomas Jogin Guest

    Default INSERT DISTINCT? how do you do that?

    I have a relations table, by that i mean a table that stores many-to-many relations.<BR>Like this:<BR>1 table called users, 1 table called permissions. And since one user can have several permissions and one permission can be assigned to several users, there is a table called perm_users between these to sort out the many-to-many relations.<BR>Anyway, i have a webinterface where i can add or delete permissions from a user. So, problem is, if someone adds a flag that is allready added, how do you solve this easily with SQL? I dont want to make the extra sql request to check what permissions the user allready have, id rather take use of some command that only inserts IF the post doesnt allready excist.<BR><BR>How do i solve this, i dont want to solve it by making primary keys in the relations table, odbc errors arent any prettier than multiple permission posts.<BR><BR>/sincerely, tomas jogin, sweden

  2. #2
    Michael Dean Guest

    Default RE: INSERT DISTINCT? how do you do that?

    Tomas,<BR>Assuming you are using a stored procedure to insert the permissions, you could add a conditional statement to your insertion code. Like:<BR><BR>Create procedure sp_AddPerms<BR> @intUserID Int,<BR> @intPermID Int,<BR>As<BR><BR><BR>If Not Exists (Select * From Perm_Users Where UserID = @intUserID And PermID = @intPermID)<BR>Begin<BR> Insert Into Perm_Users (UserID, PermID)<BR> Values(@intUserID,@intPermID)<BR>End<BR>

  3. #3
    James Zealy Guest

    Default RE: INSERT DISTINCT? how do you do that?

    Rather than query what he has, use the value that you wish to apply to determine if there is a match. If there is a match don&#039t do the insert, if there is not a match then insert the record. There should be database codes that will return a value for no records. This is a pretty basic process in a database update routine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts