mysql: if similar row exists set field=1

[jerx]

I am trying to import a buddy list into vb. Unfortunately those entries do not state friend status. Therefore I have to set it manually.

The table looks like this:
user_id contact_id user_ignore friend (=column1, column2, column3, column4)
The friend column was added by myself, because I wanted to use that for the friend status.

Now I would like to set the friend status to 1, if both users have added each other to their buddy list.

Example

If there is an entry like this
1 2 0 0
(user1 added user2 to his buddy list)

it should be turned into
1 2 0 1
(user2 is friend of user1)

if this entry exists
2 1 0 0
(user2 added user1 to his buddy list)

It would even be better to check the ignore column, too. But this would not be as important, because there are only about 100 entries which I could check manually.

Anyone able to help me on this?

Thank you in advance!

[Marco van Herwaarden]

UPDATE TABLENAME AS t1, TABLENAME AS t2
SET t1.friend = 1
WHERE t1.user_id = t2.contact_id AND t1.contact_id = t2.user_id;
Replace TABLENAME (twice) by your tablename.

Not tested, so make backup before running this.

[jerx]

Thank you so much, Marco!

The solution is so easy and a look at the mysql manual would have revealed the solution right away (first user comment). But I never had the idea to look up the update query, because I thought I needed some fancy stuff for the second part.