Deleting a line associated with a record using stored procedure
I'm trying to create a stored procedure where I can delete all data pertaining to one name. This would include data in the Order table, Line_item table, and Customer table. Should I be sub-querying differently?
CREATE PROCEDURE DELETE_CUSTOMER -- Delete a customer
@cus_id_arg DECIMAL, -- customer's ID.
@first_name_arg VARCHAR(30), -- customer’s first name.
@last_name_arg VARCHAR(40), -- customer's last name.
@cust_balance_in_arg DECIMAL(12,2) -- customer's balance
AS -- This "AS" is required by the syntax of stored procedures.
BEGIN
-- Insert the new customer with a 0 balance.
DELETE FROM CUSTOMER
WHERE order_id IN (SELECT order_id FROM customer_order where customer_id = @cus_id_arg);
END;
sql-server edited Nov 24 '18 at 6:09
Squirrel
11.9k22127
asked Nov 24 '18 at 6:08
DanDan
32
add a comment |
I'm trying to create a stored procedure where I can delete all data pertaining to one name. This would include data in the Order table, Line_item table, and Customer table. Should I be sub-querying differently?
CREATE PROCEDURE DELETE_CUSTOMER -- Delete a customer
@cus_id_arg DECIMAL, -- customer's ID.
@first_name_arg VARCHAR(30), -- customer’s first name.
@last_name_arg VARCHAR(40), -- customer's last name.
@cust_balance_in_arg DECIMAL(12,2) -- customer's balance
AS -- This "AS" is required by the syntax of stored procedures.
BEGIN
-- Insert the new customer with a 0 balance.
DELETE FROM CUSTOMER
WHERE order_id IN (SELECT order_id FROM customer_order where customer_id = @cus_id_arg);
END;
edited Nov 24 '18 at 6:09
Squirrel
11.9k22127
asked Nov 24 '18 at 6:08
DanDan
32
you need to delete fromline_itemfirst thencustomer_orderand lastcustomer
– Squirrel
Nov 24 '18 at 6:13
add a comment |
I'm trying to create a stored procedure where I can delete all data pertaining to one name. This would include data in the Order table, Line_item table, and Customer table. Should I be sub-querying differently?
CREATE PROCEDURE DELETE_CUSTOMER -- Delete a customer
@cus_id_arg DECIMAL, -- customer's ID.
@first_name_arg VARCHAR(30), -- customer’s first name.
@last_name_arg VARCHAR(40), -- customer's last name.
@cust_balance_in_arg DECIMAL(12,2) -- customer's balance
AS -- This "AS" is required by the syntax of stored procedures.
BEGIN
-- Insert the new customer with a 0 balance.
DELETE FROM CUSTOMER
WHERE order_id IN (SELECT order_id FROM customer_order where customer_id = @cus_id_arg);
END;
edited Nov 24 '18 at 6:09
Squirrel
11.9k22127
asked Nov 24 '18 at 6:08
DanDan
32
I'm trying to create a stored procedure where I can delete all data pertaining to one name. This would include data in the Order table, Line_item table, and Customer table. Should I be sub-querying differently?
CREATE PROCEDURE DELETE_CUSTOMER -- Delete a customer
@cus_id_arg DECIMAL, -- customer's ID.
@first_name_arg VARCHAR(30), -- customer’s first name.
@last_name_arg VARCHAR(40), -- customer's last name.
@cust_balance_in_arg DECIMAL(12,2) -- customer's balance
AS -- This "AS" is required by the syntax of stored procedures.
BEGIN
-- Insert the new customer with a 0 balance.
DELETE FROM CUSTOMER
WHERE order_id IN (SELECT order_id FROM customer_order where customer_id = @cus_id_arg);
END;
edited Nov 24 '18 at 6:09
Squirrel
11.9k22127
asked Nov 24 '18 at 6:08
DanDan
32
edited Nov 24 '18 at 6:09
Squirrel
11.9k22127
asked Nov 24 '18 at 6:08
DanDan
32
edited Nov 24 '18 at 6:09
Squirrel
11.9k22127
edited Nov 24 '18 at 6:09
Squirrel
11.9k22127
edited Nov 24 '18 at 6:09
Squirrel
11.9k22127
11.9k22127
asked Nov 24 '18 at 6:08
DanDan
32
asked Nov 24 '18 at 6:08
DanDan
32
asked Nov 24 '18 at 6:08
DanDan
32
32
you need to delete fromline_itemfirst thencustomer_orderand lastcustomer
– Squirrel
Nov 24 '18 at 6:13
add a comment |
you need to delete fromline_itemfirst thencustomer_orderand lastcustomer
– Squirrel
Nov 24 '18 at 6:13
you need to delete from
line_item first then customer_order and last customer– Squirrel
Nov 24 '18 at 6:13
you need to delete from
line_item first then customer_order and last customer– Squirrel
Nov 24 '18 at 6:13
add a comment |
1 Answer
1
active
oldest
votes
CASCADE
Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.
Add cascading constraints, and the delete will be easy
ALTER Customer_Order
ADD CONSTRAINT FK_CustomerID
FOREIGN KEY(Customer_ID) REFERENCES (Customer_ID)
ON DELETE CASCADE;
ALTER Line_Item
ADD CONSTRAINT FK_Order_ID
FOREIGN KEY(Order_ID) REFERENCES Customer_Order(Order_ID)
ON DELETE CASCADE;
Now, any row you delete it from Customer table will delete all corrrespending rows in Customer_Order table will be deleted, and the same for Line_Item table.
So you can write just DELETE FROM Customer WHERE Customer_ID = @Customer_ID;
answered Nov 24 '18 at 7:30
SamiSami
8,96831241
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53455632%2fdeleting-a-line-associated-with-a-record-using-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
CASCADE
Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.
Add cascading constraints, and the delete will be easy
ALTER Customer_Order
ADD CONSTRAINT FK_CustomerID
FOREIGN KEY(Customer_ID) REFERENCES (Customer_ID)
ON DELETE CASCADE;
ALTER Line_Item
ADD CONSTRAINT FK_Order_ID
FOREIGN KEY(Order_ID) REFERENCES Customer_Order(Order_ID)
ON DELETE CASCADE;
Now, any row you delete it from Customer table will delete all corrrespending rows in Customer_Order table will be deleted, and the same for Line_Item table.
So you can write just DELETE FROM Customer WHERE Customer_ID = @Customer_ID;
answered Nov 24 '18 at 7:30
SamiSami
8,96831241
add a comment |
CASCADE
Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.
Add cascading constraints, and the delete will be easy
ALTER Customer_Order
ADD CONSTRAINT FK_CustomerID
FOREIGN KEY(Customer_ID) REFERENCES (Customer_ID)
ON DELETE CASCADE;
ALTER Line_Item
ADD CONSTRAINT FK_Order_ID
FOREIGN KEY(Order_ID) REFERENCES Customer_Order(Order_ID)
ON DELETE CASCADE;
Now, any row you delete it from Customer table will delete all corrrespending rows in Customer_Order table will be deleted, and the same for Line_Item table.
So you can write just DELETE FROM Customer WHERE Customer_ID = @Customer_ID;
answered Nov 24 '18 at 7:30
SamiSami
8,96831241
add a comment |
CASCADE
Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.
Add cascading constraints, and the delete will be easy
ALTER Customer_Order
ADD CONSTRAINT FK_CustomerID
FOREIGN KEY(Customer_ID) REFERENCES (Customer_ID)
ON DELETE CASCADE;
ALTER Line_Item
ADD CONSTRAINT FK_Order_ID
FOREIGN KEY(Order_ID) REFERENCES Customer_Order(Order_ID)
ON DELETE CASCADE;
Now, any row you delete it from Customer table will delete all corrrespending rows in Customer_Order table will be deleted, and the same for Line_Item table.
So you can write just DELETE FROM Customer WHERE Customer_ID = @Customer_ID;
answered Nov 24 '18 at 7:30
SamiSami
8,96831241
CASCADE
Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.
Add cascading constraints, and the delete will be easy
ALTER Customer_Order
ADD CONSTRAINT FK_CustomerID
FOREIGN KEY(Customer_ID) REFERENCES (Customer_ID)
ON DELETE CASCADE;
ALTER Line_Item
ADD CONSTRAINT FK_Order_ID
FOREIGN KEY(Order_ID) REFERENCES Customer_Order(Order_ID)
ON DELETE CASCADE;
Now, any row you delete it from Customer table will delete all corrrespending rows in Customer_Order table will be deleted, and the same for Line_Item table.
So you can write just DELETE FROM Customer WHERE Customer_ID = @Customer_ID;
answered Nov 24 '18 at 7:30
SamiSami
8,96831241
answered Nov 24 '18 at 7:30
SamiSami
8,96831241
answered Nov 24 '18 at 7:30
SamiSami
8,96831241
answered Nov 24 '18 at 7:30
SamiSami
8,96831241
8,96831241
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53455632%2fdeleting-a-line-associated-with-a-record-using-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
you need to delete from
line_itemfirst thencustomer_orderand lastcustomer– Squirrel
Nov 24 '18 at 6:13