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

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

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...

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

[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'.

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.

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

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

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-2021 MyBB Group.