simultaneous deletion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

simultaneous deletion

Author

Topic

lamiajoyee
Starting Member

15 Posts

Posted - 2012-01-11 : 11:28:59

if i want to delete one attribute that is common in two tables in a single query how can this be accomplished?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 11:33:40

you mean delete from both the tables simlutaneously?
delete one attribute (column) alone?
isnt that same as update?
In any case, can you elaborate with some sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-11 : 11:38:51

You might be able to adapt the code in this article to do two deletes in one statement, but why can't you just use a multiple statements in a transaction?

http://weblogs.sqlteam.com/peterl/archive/2009/07/29/How-to-insert-into-two-tables-in-one-statement.aspx

lamiajoyee
Starting Member

15 Posts

Posted - 2012-01-11 : 11:47:05

i need one query because i am passing the query as a string from visual c#.if i use transaction i cant give the new line feed.is there any way to pass a transaction query from visual c# to sql database?please help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 11:47:34

see this too

http://visakhm.blogspot.com/2011/08/multiple-table-insertion-using-single.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 11:54:13

Ever consider coding a stored procedure and calling that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

lamiajoyee
Starting Member

15 Posts

Posted - 2012-01-11 : 12:00:36

sorry i am new to these stuffs.could you please explain what are you pointing at by the term "stored procedure"?

lamiajoyee
Starting Member

15 Posts

Posted - 2012-01-11 : 12:06:41

i am working on stored procedure, could you please share some links that might be useful to me? i am totally new to this thing

lamiajoyee
Starting Member

15 Posts

Posted - 2012-01-11 : 12:12:06

i am working on stored procedure, could you please share some links that might be useful to me? i am totally new to this thing

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 12:16:37

do you have sql server client tools installed?

i.e. SQL Server Management Studio?

http://msdn.microsoft.com/en-us/library/ms345415.aspx

Can you explain what you need to do?

If you want to delete 1 attribute common in 2 tables..are the column names the same in the tables?

In the Simplest of forms

CREATE PROC mySproc99
@attrib varchar(10)
AS
DELETE FROM Table1 WHERE Col = @Attrib
DELETE FROM Table2 WHERE Col = @Attrib
Return
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

lamiajoyee
Starting Member

15 Posts

Posted - 2012-01-11 : 13:05:42

yes i have management studio.i am doing a project in visual c# where there are two fields "vehicle","customer".when a customer takes a vehicle,his id is passed to "vehicle".but when he returns the vehicle,his id is removed.but i have to delete that id from vehicle too.how can this be accomplished?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 13:09:15

When you say "passed" to Vehicle, and delete that id from vehicle

I'm assuming you want to update the vehicles row in the vehicle table

Sort of check in and check out

Isn't that 2 separate operations?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 13:12:21

quote:
Originally posted by lamiajoyee

yes i have management studio.i am doing a project in visual c# where there are two fields "vehicle","customer".when a customer takes a vehicle,his id is passed to "vehicle".but when he returns the vehicle,his id is removed.but i have to delete that id from vehicle too.how can this be accomplished?

there's a design flaw in this
why should customerid be included in vehicle table at all?
customerid is not an attribute directly related to vehicle so ideally you should have a third table called vehicle_allocation which will have
vehicleid,customerid,loaneddate,returneddate

then each allocation of vehicle will add a record in above table with loaneddate indicating date when it was alloted and while returning back populate returneddate with date when it was returned. otherwise returneddate will just have default value which is NULL indicating its not alloted to anyone. using this approach you can even track history of vehicle also ie whom all it was alloted to and when. if you want, you can even have a bit field (current/active) to indicate which is last (active) record

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lamiajoyee
Starting Member

15 Posts

Posted - 2012-01-11 : 13:25:36

its like this------i have to keep track of the customers and the respective vehicle he has taken.when he gives it back,our job with him is finished.so we delete him.the problem arises because "customer id" is a foreign key in the table "vehicle" and the primary key in table "customer" which is not nullable in "vehicle".if i delete the "customer id" in "customer",what will i do with "vehicle",cause the "customer id" will become blank which is not permitted in "vehicle".

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 13:28:55

What visakh said

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

lamiajoyee
Starting Member

15 Posts

Posted - 2012-01-11 : 13:31:08

sorry for my last post....it got published unintentionally. i am thinking about the flaw.thank you for your consideration.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 14:29:29

You need

TABLE: Vehicle, Customer and Vehicle_Customer

Vehicle has everything about the Vehicle
Customer has everything about a customer

Vehicle_Customer has everything about when a customer takes a vehicle and when it was returned (in some fashion or another)

You really should NOT be deleting Customers

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 00:07:11

quote:
Originally posted by lamiajoyee

sorry for my last post....it got published unintentionally. i am thinking about the flaw.thank you for your consideration.

welcome
Customer is a seperate entity so there should be a separate table for it
existence of customer is in no way related to he taking a vehicle

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources