mysql issue plz

[thenotorious_flag]

Hi guys, I am trying to delete the user. my User gets deleted successfully.

my userid is realted to table2 which has refernce of userid.

I mean like i have 3 records and 2 records are related to userid 2.

now i delete userid 2, i want that also in should reflect that thinh in table 2.

I want it should update the table 2 userd value to null where userid was 2.

i am trying to do it in 2 queries but it is now working.

no err is tthrown,

first query make update using in operator (i think my query structure is wrong)

update userid
set userid = null
where userid in ('form.userid')

then i make th delete query?

DELETE FROM users
WHERE
userid = <cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.userid)#">

can u please tell me how this structure can be achieved.

well i am no good at procedures. if this can be done through procedures, can anybody tell how can it done..

Cheers

[Dan_Bracuk]

If you had a normalized db, the userid would be a foreign key in the 2nd table. You would not be able to set it to null. You would have to delete the records. Plus, you would have to delete the related records before you delete the ones in the users table.

An alternative approach is to delete nothing and simply de-activate the user.

[BKBK]

Data is money. You spent either time or money to get it, so you shouldn't delete a user. As Dan Bracuk suggests, create the boolean column isActive.

Unless, of course, the data is wrong. Then I would simply do a delete in all the tables, and be done with it. No updates and stuff.