SQL query (deleting things based on other table contents)

[NEWSBOT3]

ok, my brain hurts.

I have two tables,

table ServiceItems, which has a value ServiceID

table manage_domains which has ServiceID value too

I need to delete the whole row from serviceitems if the serviceID doesnt match one on manage_domains

(i need to make sure domains we don't manage arent being billed)

I can't for the life of me think of how to structure a query to do it.
not that it should matter, but this is for a php script.

[wu_trax]

DELETE FROM ServiceItems WHERE ServiceID NOT IN (SELECT serviceID FROM manage_domains)

or something like that. Most likely there is something far more clever, but that is what i would try. Not sure if you use mysql, if yes, it probably wont work. mysql doesnt like subselects.

[NEWSBOT3]

aha, NOT IN

thats a point, i am using mysql. shoulda said that

[Structural Integrity]

Originally Posted by wu_trax

DELETE FROM ServiceItems WHERE ServiceID NOT IN (SELECT serviceID FROM manage_domains)

or something like that. Most likely there is something far more clever, but that is what i would try. Not sure if you use mysql, if yes, it probably wont work. mysql doesnt like subselects.

I recently learned about the keywords IN and NOT IN and subqueries. I use them in combination with MS Access. They are really cool.
Now I only need a multi table UPDATE an INSERT INTO and I'm set.

What is the easiest way dealing with auto-increment fields where the auto-increment value is used in other tables too?
Say I have two linked tables and want to insert a record into both. The primary key of table A is an auto increment field. The foreign key of table B is linked to the primary key of table A. I now have to do an INSERT INTO on table A, and then a SELECT MAX(auto_inc_field) to get the auto increment value, and then do another INSERT INTO in table B. This is not 100% safe because in theory another person can insert a record between the first INSERT and SELECT and then the MAX(auto_inc_field) will return the wrong value. Is there any way of inserting and selecting the last inserted value in one query?

[Dante Hicks]

Subselects work in MySQL 4.1

[wu_trax]

Originally Posted by NEWSBOT3

aha, NOT IN

thats a point, i am using mysql. shoulda said that

If dante says it works in a newer version maybe you are lucky.
But i forgot about the best solution. thats foreign keys. iirc from my DB-lectures in uni there is a keyword that lets you delete a row in a specific table and with it all rows in other table that reference to it by a defined foreign key. That, ofc, only works if your mysql-version and the table type support foreign keys. i've been working with a rather old version of mysql at work for the last year, so i dont remmeber that keyword

[wu_trax]

Originally Posted by Structural Integrity

I recently learned about the keywords IN and NOT IN and subqueries. I use them in combination with MS Access. They are really cool.
Now I only need a multi table UPDATE an INSERT INTO and I'm set.

What is the easiest way dealing with auto-increment fields where the auto-increment value is used in other tables too?
Say I have two linked tables and want to insert a record into both. The primary key of table A is an auto increment field. The foreign key of table B is linked to the primary key of table A. I now have to do an INSERT INTO on table A, and then a SELECT MAX(auto_inc_field) to get the auto increment value, and then do another INSERT INTO in table B. This is not 100% safe because in theory another person can insert a record between the first INSERT and SELECT and then the MAX(auto_inc_field) will return the wrong value. Is there any way of inserting and selecting the last inserted value in one query?

for php/mysql there is the php function mysql_insert_id that returns the last inserted auto increment id. maybe there is something like that in your programming language of choice?
If not, you could do the select max on table A first, add one and then use and then use the number for both tables. There is still a 'hole' between the select(max) and the insert, but you always have the same id for both tables and if you check if the insert in table a worked before inserting in table b you are at least sure that there is no wrong data in table b.
or maybe you could do something with threads (make sure that there are never more than one thread in that part of the code)? (im guessing here, i forgot far too much stuff in the last year )

But you are right, there should be some more elegant way to do this.

[Gayle29uk]

SI: LAST_INSERT_ID() is your friend here. And yes, it's thread safe.

[NEWSBOT3]

gah, i dont think mysql likes me

still, i shall play more when i get in in two hours.