Delete from table where id doesnt exist in another

[rudeboi]

Hello, I have a problem like this ... My website Uploads and stores pictures in a MySql database. My problem is that i dont have any kind of redundancy check on the database table it just adds the new picture. I'm ok with that ... but i wanted to include an admin function that deletes the pictures from the database that arent used ... my setup is as follows:

'users' table:
- username password and all that good stuff
- pic int unsigned default 1

'pics' table
- id int not null auto_increment primary key,
- name varchar(30) not null,
- type varchar(30) not null,
- size int(11) not null,
- content mediumblob not null

not that i think all that matters but ... putting it there just incase

So, was thinking of doing something like :

DELETE FROM pics WHERE id != users.pic 

or something like that. Any Help would be appriciated ... thanks

P.S. and plz excuse my excessive attempt at detailedness ... this is my first ever forum post

Thanks in advanced

[Anyango]

Hey rudeboi,

Welcome to WebmasterWorld!

Try something like this

DELETE FROM pics where id not in (select pic from users)

What it does is that the subquery will get all the 'pic' values from your users table and then the main query will delete all data from pics table where id is not in one of the values that subquery returned.

Try it on a Test Database first, i typed all the message in a rush.

[rudeboi]

Wow Thanks so much that worked perfectly