performing a delete on a relational data

junkie_doodle

i'm trying to figure out an efficient way to perform a delete action on a relational database on four tables at the same time.

here are my tables

table1

table2

id
table_1->id

table3

id
table_2->id

table4

id
table_3->id

and i just would like to use a single query to delete all records related to table_1-> id

i am sure there's a clear way for this with ANSI SQL but i just can not figure out how to do this.

i'll be happy if you help me soon or i'll drive mad iguess 😉

thanx

Anon

I don't even know if this is possible, but you could try it 😛

"DELETE FROM table1, table2, table3, table4 WHERE table1.id = table2.id AND table3.id = table2.id AND table4.id = table3.id AND table1.id = $idToDelete"

BACK UP ANY EXISTING DATA BEFORE TRYING THIS!
I'm not exactly sure myself what it will do...

junkie_doodle

i've already tried this..

it doesnt work when i try to perform a delete on multiple tables and it seems to be there's something wrong with the comma seperated table names..

i won't be able to understand why if this is impossible with ANSI SQL

anyway thanks...

junkie_doodle

Well..

i have just learned that it is not possible with ANSI SQL

i think ,it is really hard to understand why but i have nothing to do

what i will do is performing query over query and achive my simple request..

And i do think that THIS IS NOT FAIR!!

[deleted]

Indeed, it is not fair. That is why proper databases such as PostgreSQL, Oracle, Informix and DB2 have Foreign key constraints with cascading delete.

MySQL does not do relations (because the creators think speed is more important)
So any relation that you want to use has to be implemented in your scripts, not in the database.

In PostgreSQL for example, you can use foreign-key constraints. This means the data in the second table not only 'links' to data in the first table, the database will also enforce this. You cannot enter data into FK column of the second table if that data does not already existin the PK of the 'parent' table.

Cascading delete means that if you delete a record in the parent table, all records that reference that record are deleted also.

I've said it before and I'll keep saying it untill someone listens:
MySQL is bad, PostgreSQL is good.

junkie_doodle

I do know that you're right.

But we're developers and we have to use the platform told us to be used. In my oppinion PgSql is much better too.

I have seen an article (probably in phpbuilder.com again) about benchmark tests of MySql and PgSql. It was obvious that PgSql was more efficient ,powerfull and FASTER when there's much connections.

I just couldn't find an explanation to my situation. I think i'll have to hide behind of the speed of unix system and MySql and fill my scripts with queries.

But this is not a professional move. I'll be ashamed of my code ,my platform and my project. Because this is about my respect to my project. MySql may be more popular or it can be supported by more other development companenets but this doesn't make us enjoying with the work we're doing.

This is not a prefered job. I mean being a computer programmer. Most of us would choose to be doing something else. But we do feel "productive" while over loading our brains cortex. And from my point of view this is what i care about my job. It's not money or anything else.

Please just let us enjoy with our job.

I hope that there'll someone understand my feelings.

sevex

You think you've got it bad? At my job I have to code in coldfusion 😛

Anon

At least you got a job, dear...

Think of those professionals in US and elsewhere, hardcore programmers and DB admins, who are jobless bcoz of recession in US, Japan, and elsewhere.

Job satisfaction these days has become a joke.

atleast you got a job, a schedule to program something, whatever...
Programming is fun, whatever way you do.

i agree to some extent when programming is as frustrating as anything say e.g. you have to use 10 if conditions to achieve something... or 10 case statements. and u must be wondering is there an alternative to it... Given the time contraint of project, resources, and other factors, u sometimes have no choice other than 'just do it'.
I believe in that. Clients are kings, they come 1st.

And regarding choice of DB, it all depends on clients requirements, management policy, boss's rules, that sometimes we are left with choices like MySSQL or MSSQL. there is little you can do about convincing ur boss to put in PSQL or whatever, when he himself has never heard of it too...

The point is Stick to your job.... Satisfaction is secondary these days.

Good luck !