November 4, 2008 at 3:49 am
Hi there
I have two tables ( tbl_user and tbl_user_role ). tbl_user_role is joined to tbl_user by a foreign key (user-id). I am simply trying to use delete a delete command and since the table are joined, it should be deleting on a row on both table.
Here are my tables:
tbl_user tbl_user_role
user_id user_id (foreign key)
user_name role_id
user_surname
user_email
user_pw
dpt_id
user_review
Here is what I tried: (the command below doesn't delete anything)
DeleteCommand="
DELETE [tbl_user]
FROM [tbl_user]
JOIN [tbl_user_role] ON [tbl_user].[user_id]= [tbl_user_role].[user_id]
WHERE
[user_id] = @original_user_id AND
[user_name] = @original_user_name AND
[user_surname] = @original_user_surname AND
[user_email] = @original_user_email AND
[user_pw] = @original_user_pw AND
[dpt_id] = @original_dpt_id"
I also tried this (this one only deletes the row on the second table. tbl_user_role )
DELETE FROM [tbl_user]
WHERE [user_id] = @original_user_id AND
[user_name] = @original_user_name AND
[user_surname] = @original_user_surname AND
[user_email] = @original_user_email AND
[user_pw] = @original_user_pw AND
[dpt_id] = @original_dpt_id AND
DELETE FROM [tbl_user_role] WHERE [user_id] = @original_user_id "
This is my delete parameters
Any hints would be appreciated
November 4, 2008 at 4:05 am
I advise to use aliasses in queries !
- a delete only operates on a single object !
so you should perform TWO delete statements !
Since you have DRI implemented (FK) , you should take care of the order of execution of your queries !
You should also provide indexes for all foreign key columns (in the exact order as defined in the foreign key relationship !)
/* delete roles */
DELETE R
FROM [tbl_user] U
INNER JOIN [tbl_user_role] R
ON U.[user_id]= R.[user_id]
WHERE
U.[user_id] = @original_user_id AND
U.[user_name] = @original_user_name AND
U.[user_surname] = @original_user_surname AND
U.[user_email] = @original_user_email AND
U.[user_pw] = @original_user_pw AND
U.[dpt_id] = @original_dpt_id
/* Delete users */
DELETE U
FROM [tbl_user] U
WHERE
U.[user_id] = @original_user_id AND
U.[user_name] = @original_user_name AND
U.[user_surname] = @original_user_surname AND
U.[user_email] = @original_user_email AND
U.[user_pw] = @original_user_pw AND
U.[dpt_id] = @original_dpt_id
Don't do this CURSORbased, but use an extra join with your "users to delete object"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2008 at 4:30 am
Hi
Thank you very much for your reply .. I tried your method and it didn't work for me. The data would not be deleted.
I manage to fix it with the following command:
DeleteCommand="
DELETE FROM [tbl_user_role] WHERE [user_id] = @original_user_id
DELETE FROM [tbl_user] WHERE [user_id] = @original_user_id"
It seems very simplistic but it does work...
November 5, 2008 at 5:17 am
the original queries contained this where clause:
U.[user_id] = @original_user_id AND
U.[user_name] = @original_user_name AND
U.[user_surname] = @original_user_surname AND
U.[user_email] = @original_user_email AND
U.[user_pw] = @original_user_pw AND
U.[dpt_id] = @original_dpt_id
Off course all parameter values must be supplied and be correct because the row to be deleted must exactly match the given criteria.
Supplying only the U.[user_id] = @original_user_id will only match user_id. Maybe that was one of your issues.:unsure:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply