dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
1047
share rss forum feed


Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2

Need help with mysql command

Hey I was wondering if there was any resident MYSQL experts here. I needed to come up with a mysql command to delete all entries in a table that had a column with a time older than 60 seconds.

Below is an example, if the "expires" column is older than 60 seconds, I need that row removed from the table.

Anyone that can help me? Im not an expert with MYSQL (5.1.57)
Thanks!

 
+----+-----------+-------------------+---------------------------------+----------+-----------------------+---------------------+-------+-----------------------+-------+---------------------+-------+--------+--------------------------+--------------------+---------+------------------------+--------+-------------------------------------------------+
| id | username  | domain            | contact                         | received | path                  | expires             | q     | callid                | cseq  | last_modified       | flags | cflags | user_agent               | socket             | methods | ruid                   | reg_id | instance                                        |
+----+-----------+-------------------+---------------------------------+----------+-----------------------+---------------------+-------+-----------------------+-------+---------------------+-------+--------+--------------------------+--------------------+---------+------------------------+--------+-------------------------------------------------+
| 39 | xxxxxxxxx | sip.xxxxxxxxxx.ca | sip:xxxxxxxxx@24.102.xx.xx:5061 | NULL     | <sip:lb@127.0.0.1;lr> | 2013-01-07 21:11:49 | -1.00 | 16f61398@10.20.30.150 | 17161 | 2013-01-07 21:10:49 |     0 |      0 | OBIHAI/OBi100-1.3.0.2765 | udp:127.0.0.1:5062 |    4767 | uloc-50eb03c5-61e2-e61 |      0 | <urn:uuid:00000000-0000-0000-0000-9cadef1098d8> |
+----+-----------+-------------------+---------------------------------+----------+-----------------------+---------------------+-------+-----------------------+-------+---------------------+-------+--------+--------------------------+--------------------+---------+------------------------+--------+-------------------------------------------------+
1 row in set (0.00 sec)
 

--
OptionsDSL Wireless Internet
»www.optionsdsl.ca


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7

1 recommendation

Try something like:
SELECT * FROM your_table_name
WHERE DATE_ADD(NOW(), INTERVAL -60 SECOND) > expires
 
If that returns the records you'd expect to delete, change "SELECT *" to "DELETE"


Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2
said by cdru:

Try something like:

SELECT * FROM your_table_name
WHERE DATE_ADD(NOW(), INTERVAL -60 SECOND) > expires
 
If that returns the records you'd expect to delete, change "SELECT *" to "DELETE"

Awesome thanks that worked perfectly!

--
OptionsDSL Wireless Internet
»www.optionsdsl.ca


Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2
reply to cdru
Ok thanks again for that command, it worked perfectly, I ran into needing another command, this one I think might be more complicated.

I have some duplicate entries in a database, I need a way to find the duplicate entries (based on username), and delete all but the one with the highest ID.

Here is an example. The entries for duser12 are all identical (but may not always be the case except the username part will always be constant), I need the ID 86 Newest one. This only shows one username duplicated, but at any given time many usernames may be duplicated (example is sterilized). This is waaay beyond anything I can do, I can pay in kudos! Thanks!
+----+-----------+-------------------+----------------------------------+----------+-----------------------+---------------------+-------+------------------------+-------+---------------------+-------+--------+--------------------------+--------------------+---------+------------------------+--------+-------------------------------------------------+
| id | username  | domain            | contact                          | received | path                  | expires             | q     | callid                 | cseq  | last_modified       | flags | cflags | user_agent               | socket             | methods | ruid                   | reg_id | instance                                        |
+----+-----------+-------------------+----------------------------------+----------+-----------------------+---------------------+-------+------------------------+-------+---------------------+-------+--------+--------------------------+--------------------+---------+------------------------+--------+-------------------------------------------------+
| 73 | duser12   | xxx.xxxxxxxxxx.ca | sip:duser12@xxx.xxx.xxx.xxx:5060 | NULL     | <sip:lb@127.0.0.1;lr> | 2013-01-10 20:34:50 | -1.00 | 91dd4278@10.40.36.112  | 56687 | 2013-01-10 20:33:50 |     0 |      0 | OBIHAI/OBi100-1.3.0.2765 | udp:127.0.0.1:5062 |    4767 | uloc-50ee22a8-3fb2-d72 |      0 | <urn:uuid:00000000-0000-0000-0000-9cadef109e85> |
| 81 | duser12   | xxx.xxxxxxxxxx.ca | sip:duser12@xxx.xxx.xxx.xxx:5060 | NULL     | <sip:lb@127.0.0.1;lr> | 2013-01-10 20:34:50 | -1.00 | 91dd4278@10.40.36.112  | 56687 | 2013-01-10 20:33:50 |     0 |      0 | OBIHAI/OBi100-1.3.0.2765 | udp:127.0.0.1:5062 |    4767 | uloc-50ee22a8-3fb2-d72 |      0 | <urn:uuid:00000000-0000-0000-0000-9cadef109e85> |
| 84 | ddufresne | xxx.xxxxxxxxxx.ca | sip:ddufresne@24.102.xx.xx:5061  | NULL     | <sip:lb@127.0.0.1;lr> | 2013-01-10 20:34:50 | -1.00 | 65d910d2@10.20.30.150  | 55598 | 2013-01-10 20:33:50 |     0 |      0 | OBIHAI/OBi100-1.3.0.2765 | udp:127.0.0.1:5062 |    4767 | uloc-50ee22a8-3fad-1   |      0 | <urn:uuid:00000000-0000-0000-0000-9cadef1098d8> |
| 86 | duser12   | xxx.xxxxxxxxxx.ca | sip:duser12@206.248.xxx.xxx:5060 | NULL     | <sip:lb@127.0.0.1;lr> | 2013-01-10 20:34:50 | -1.00 | 91dd4278@10.40.36.112  | 56687 | 2013-01-10 20:33:50 |     0 |      0 | OBIHAI/OBi100-1.3.0.2765 | udp:127.0.0.1:5062 |    4767 | uloc-50ee22a8-3fb2-d72 |      0 | <urn:uuid:00000000-0000-0000-0000-9cadef109e85> |
| 87 | glamb     | xxx.xxxxxxxxxx.ca | sip:glamb@24.102.xx.x:5060       | NULL     | <sip:lb@127.0.0.1;lr> | 2013-01-10 20:35:00 | -1.00 | 70435a7d@192.168.1.136 | 51396 | 2013-01-10 20:34:00 |     0 |      0 | OBIHAI/OBi100-1.3.0.2765 | udp:127.0.0.1:5062 |    4767 | uloc-50ee22a8-3fab-1   |      0 | <urn:uuid:00000000-0000-0000-0000-9cadef10c515> |
+----+-----------+-------------------+----------------------------------+----------+-----------------------+---------------------+-------+------------------------+-------+---------------------+-------+--------+--------------------------+--------------------+---------+------------------------+--------+-------------------------------------------------+
 

--
OptionsDSL Wireless Internet
»www.optionsdsl.ca


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7
Presuming id is unique and always incrementing, we need to first get the IDs for the highest unique usernames:
SELECT MAX(id)
FROM your_table_name
GROUP BY username
 

If you need to determine uniqueness based on additional columns (e.g. username and contact), add the additional columns to the Group By statement.

Once we have all the unique columns, we can negate the above, selecting the rows that don't match the columns we selected above:
SELECT * FROM your_table_name
WHERE id NOT IN (
   SELECT * 
   FROM (
       SELECT MAX(id)
       FROM your_table_name
       GROUP BY username
   )
)
 

Again, test that this is actually selecting the rows you want to delete, then change the SELECT * to DELETE. No guarantees on the correctness of my statements so hopefully you aren't using this on a production system or at least one you can't afford to delete something you shouldn't have.


Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2
Wow you are good

Seems to do the trick just the way it is .
Thanks so much!
--
OptionsDSL Wireless Internet
»www.optionsdsl.ca


Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2
reply to cdru
Hey again. I just got around to trying to use this command to DELETE, but I got an error. It seems to work perfectly for the SELECT. Any ideas?

 
root@sip:~# mysql kamailio -u root  -e "DELETE FROM location WHERE id NOT IN (SELECT MAX(id) FROM location GROUP BY username)"
ERROR 1093 (HY000) at line 1: You can't specify target table 'location' for update in FROM clause
root@sip:~# 
 

--
OptionsDSL Wireless Internet
»www.optionsdsl.ca


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7
You may need to wrap the nested select inside another select to create a derived table, breaking the direct relationship of "location" of the subquery from that of the delete. Try the delete on the text I gave in the 2nd example.


Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2
I get a new error below:

 
root@sip:~# mysql kamailio -u root  -e "SELECT * FROM location WHERE id NOT IN (SELECT * FROM (SELECT MAX(id) FROM location GROUP BY username ))"
ERROR 1248 (42000) at line 1: Every derived table must have its own alias
 

--
OptionsDSL Wireless Internet
»www.optionsdsl.ca


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7
try
DELETE FROM your_table_name
WHERE id NOT IN (
   SELECT *
   FROM (
       SELECT MAX(id)
       FROM your_table_name
       GROUP BY username
   ) t
)
 


Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2
Perfect!.. Thanks so much!


Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2
reply to cdru
I really hate digging up this thread, but I ran into a new problem once I registered more than one ATA to an account, it was deleting a valid entry.

I still need it to delete all but the newest entry based on username, (ID being the newest), but also with the same cseq entry.

So if I have 5 entries with the same username, and 3 of those have the same cseq, and 2 of them have a different cseq, I need it to keep the newest unique cseq for the username.

This will allow it to keep the latest entry for each ATA registered.

Im not sure if that made sense.

Thanks for any help
--
OptionsDSL Wireless Internet
»www.optionsdsl.ca


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7
said by Inssomniak:

I still need it to delete all but the newest entry based on username, (ID being the newest), but also with the same cseq entry.

Already anticipated and answered in the 5th post of this thread.