1

I have two variables,

UserID, and User_Links,

As example,

UserID = 10098729
User_Links = 10432,78723,78263,21829,759273,108797,21167 (this is a string)

The table I am considering is set up as follows:

Table Name: User_Links

Link_ID  User_1    User_2
1        89982100  10098729
2        10098729  12482109
3        13210012  27919828
4        17781722  10098729

Essentially, this table links the User to other Users by ID.

My variable UserID is the user, and the User_Links string are the users that I want to link the current user to (UserID).

However, I only want to add these values (adding the UserID in the User_1 column and the User_Links values to User_2) to my database IF the link is not already there.

I am just looking for advice, and possibly the best way to write this query.

So far, I have considered creating (from the string) and iterating through the array of User_Links and checking if each value exists in the database, and then running a query to add it if it does not. (this seems so inefficient)

I have also considered using an IN statement, but I am not sure where to go from there.

hakre
  • 193,403
  • 52
  • 435
  • 836
TaylorMac
  • 8,882
  • 21
  • 76
  • 104

1 Answers1

4
  • Add a unique constraint to (User_1, User_2)
  • If your links are symmetric (meaning that "foo"->"bar" is the same as "bar"->"foo") always insert the rows such that User1 < User2.

Then just insert rows, ignoring errors due to duplicate keys.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452