• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
db query problem

#1
[eluser]Bigil Michael[/eluser]
my db is like this

Code:
id  username  lastactive   member_id        selected_member  status
1 Bigil       1330086806     1770               1791          1
2 shine       1330071220     1792               1770          1
3 sajith      576576575      1791               1770          1
my requirement is to change the status to 0 if member_id and selected_member are cross connected
ie rows 1 and 3
anybody knows how to write the query to get the required result.

thanks in advance...

#2
[eluser]Mauricio de Abreu Antunes[/eluser]
What is cross connected?

#3
[eluser]Bigil Michael[/eluser]
please look at the first row and third row there is a connection between them.
member_id of first row = selected member of third row and member id of third row = selected member of first row.

i want to change the status of these row to '0'.


#4
[eluser]aquary[/eluser]
Not sure if you could do that within a normal single query....

Maybe a subquery might work ? I assume from the example that both row 1 and row 3 will be updated.

Code:
UPDATE the_table SET status=0 WHERE id IN (
   SELECT TB1.id FROM the_table TB1
     JOIN the_table TB2
        ON(TB1.member_id=TB2.selected_id)
           OR (TB1.selected_id=TB2.member_id)
   );


Try runnning the subquery part to see if the ids are those to be updated.

#5
[eluser]CroNiX[/eluser]
You should use transactions for this kind of thing. If the first update doesn't work, it won't try the second. If the second update fails, it will roll-back the first update so neither get updated.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.