Need to delete old records in table

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

All the old userteam info needs to be deleted. tc_teams has new info so I need to match team_id from tc_teams to tc_userteams and delete all non matching team_id's from userteams.

Something like:

Code:

DELETE * FROM `nuke_tc_userteams` JOIN `nuke_tc_teams`


WHERE `team_id` != `team_id`

or

Code:

DELETE FROM nuke_tc_userteams


(SELECT * FROM nuke_tc_teams a, nuke_tc_userteams b, 


WHERE a.team_id != b.team_id)

Neither are working. Can I use alias in phpmyadmin?

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Code:




DELETE FROM nuke_tc_userteams


WHERE nuke_tc_userteams.team_id != nuke_tc_userteams.team_id;

I'm just showing you the syntax and drawing from the examples you gave.

Posted: Mon Nov 06, 2006 8:59 am

Raven wrote:

Code:




DELETE FROM nuke_tc_userteams


WHERE nuke_tc_userteams.team_id != nuke_tc_userteams.team_id;

I'm just showing you the syntax and drawing from the examples you gave.

But this doesn't match anything in the tc_teams table.

To clarify: My tc_teams table has all good fresh data and I need to clean the tc_userteams and delete all record that don't have a corrosponding team_id in the tc_teams table.

Posted: Mon Nov 06, 2006 9:04 am

If I do this

Code:

DELETE FROM nuke_tc_userteams WHERE nuke_tc_userteams.team_id != nuke_tc_teams.team_id

It tells me

#1109 - Unknown table 'nuke_tc_teams' in where clause

So I JOIN tc_teams

Code:

DELETE FROM nuke_tc_userteams JOIN nuke_tc_teams WHERE nuke_tc_userteams.team_id != nuke_tc_teams.team_id

and it still errors.

Posted: Mon Nov 06, 2006 9:39 am

#1109 - Unknown table 'nuke_tc_teams' in where clause

That shows that its looking for the table, so perhaps you have the table name wrong. If thats the case, then niether will work, Im only saying that it is attempting to do what you are looking to do. a problem you might run into but Im not sure, if you say delete from table one where field != field of table 2. Then you will be wiping it all out(I believe).

If table1.field1=1,2,3,4,5,6,7.
and you have table2.field2=0,1,2,3,4,5,6,7.
It may process like this, Delete from table2 WHERE table2.field2 !=2; Just using 2 from table1, So basically, even though it matches somewhere, it would run that sql and then you just deleted 0,1,3,4,5,6,7.
I hope this makes sense, My recomendation because I really am not that good at sql code yet;), would be to let php handle it with a loop, then set php to run an sql that deletes only the rows that you need to be deleted. Perhaps sql will call less resources but hopefully you understand what I mean and will be able to make it work.

Good Luck.

Posted: Mon Nov 06, 2006 10:05 am

Try this.

DELETE ut FROM nuke_tc_userteams AS ut, nuke_tc_teams AS t
WHERE ut.team_id != t.team_id

or if that doesn't work, try

DELETE ut FROM nuke_tc_userteams AS ut, nuke_tc_teams t
WHERE ut.team_id != t.team_id

Posted: Mon Nov 06, 2006 10:20 am

It's truncating the whole userteam table. I just wont the ones that don't have a corrosponding team_id in the tc_teams table.

Example:

user Donovan [3rd ID] has uid = 2 and team_id = 1 in the tc_userteam table

In the tc_teams table team_id 1 no longer exist.

And then there is:

Manary [3rd ID] has uid = 8 and team_id=123 in tc_userteams. There is a team_id 123 in the tc_teams that is good data.

Posted: Mon Nov 06, 2006 10:41 am

As I said, I was just trying to help you get the syntax right Smile

, not the logic. I'll get back to this later if no one else has resolved it. I'm really busy with something right now.