+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: db query problem (/showthread.php?tid=49540)
db query problem - El Forum - 02-24-2012
[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 1ie rows 1 and 3
anybody knows how to write the query to get the required result.
thanks in advance...
db query problem - El Forum - 02-24-2012
[eluser]Mauricio de Abreu Antunes[/eluser]
What is cross connected?
db query problem - El Forum - 02-24-2012
[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'.
db query problem - El Forum - 02-24-2012
[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.
db query problem - El Forum - 02-25-2012
[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.