Sql Server delete syntaxRestoring SQL Server databases - tips and tricks?SQL Server — optimization...
What differences exist between adamantine and adamantite in all editions of D&D?
Do you have to have figures when playing D&D?
change hotend thermistor input in Marlin
Why does smartdiagram replace the Greek letter xi by a number?
2019 gold coins to share
Amplitude of a crest and trough in a sound wave?
Ability To Change Root User Password (Vulnerability?)
I've been given a project I can't complete, what should I do?
Was planting UN flag on Moon ever discussed?
Who voices the small round football sized demon in Good Omens?
Increase speed altering column on large table to NON NULL
What does the pair of vertical lines in empirical entropy formula mean?
empApi with Lightning Web Components?
How can one's career as a reviewer be ended?
How do we say "within a kilometer radius spherically"?
Why did Intel abandon unified CPU cache?
Can we completely replace inheritance using strategy pattern and dependency injection?
Why is Na5 not played in this line of the French Defense, Advance Variation?
Is the use of umgeben in the passive unusual?
Grep Match and extract
Prob. 5, Sec. 6.2, in Bartle & Sherbert's INTRO TO REAL ANALYSIS, 4th ed: How to show this function is strictly decreasing using derivative
Did Apple bundle a specific monitor with the Apple II+ for schools?
bash vs. zsh: What are the practical differences?
Why is long-term living in Almost-Earth causing severe health problems?
Sql Server delete syntax
Restoring SQL Server databases - tips and tricks?SQL Server — optimization techniques for rebuilding very large tableMySQL Large DELETE with JOINsSQL Server 2008R2 Migration to new Platform - What Steps Required?How to group SQL Server files for restoring databaseUser-shared queries: Dynamic SQL vs. SQLCMDOrphan records after delete statement in stored procedureSQL Server 2012 Import Wizard appending new data onlyMigrate on-premise SQL Server databases to Azure SQL DatabaseThe MS documentation on FILESTREAM makes it sound like it should only be used for archiving unstructured data. Am I misunderstanding this?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I recently faced a delete syntax I was unaware of.
delete #fooTbl from #fooTbl where attr ='some'
The Official Microsoft documentation states:
DELETE FROM [database_name . [ schema ] . | schema. ] table_name
[ WHERE <search_condition> ]
[ OPTION ( <query_options> [ ,...n ] ) ]
[; ]
i have some perplexity about the command I wrote:
- What is and what is needed that parameter before the delete keyword (In my example coincides with table name)?
- What is the best practice?
sql-server t-sql delete
asked 10 hours ago
s.demuros.demuro
644
add a comment |
I recently faced a delete syntax I was unaware of.
delete #fooTbl from #fooTbl where attr ='some'
The Official Microsoft documentation states:
DELETE FROM [database_name . [ schema ] . | schema. ] table_name
[ WHERE <search_condition> ]
[ OPTION ( <query_options> [ ,...n ] ) ]
[; ]
i have some perplexity about the command I wrote:
- What is and what is needed that parameter before the delete keyword (In my example coincides with table name)?
- What is the best practice?
sql-server t-sql delete
asked 10 hours ago
s.demuros.demuro
644
add a comment |
I recently faced a delete syntax I was unaware of.
delete #fooTbl from #fooTbl where attr ='some'
The Official Microsoft documentation states:
DELETE FROM [database_name . [ schema ] . | schema. ] table_name
[ WHERE <search_condition> ]
[ OPTION ( <query_options> [ ,...n ] ) ]
[; ]
i have some perplexity about the command I wrote:
- What is and what is needed that parameter before the delete keyword (In my example coincides with table name)?
- What is the best practice?
sql-server t-sql delete
asked 10 hours ago
s.demuros.demuro
644
I recently faced a delete syntax I was unaware of.
delete #fooTbl from #fooTbl where attr ='some'
The Official Microsoft documentation states:
DELETE FROM [database_name . [ schema ] . | schema. ] table_name
[ WHERE <search_condition> ]
[ OPTION ( <query_options> [ ,...n ] ) ]
[; ]
i have some perplexity about the command I wrote:
- What is and what is needed that parameter before the delete keyword (In my example coincides with table name)?
- What is the best practice?
sql-server t-sql delete
sql-server t-sql delete
asked 10 hours ago
s.demuros.demuro
644
asked 10 hours ago
s.demuros.demuro
644
asked 10 hours ago
s.demuros.demuro
644
asked 10 hours ago
s.demuros.demuro
644
asked 10 hours ago
s.demuros.demuro
644
644
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
What is and what is needed that parameter before the delete keyword
(In my example coincides with table name)?
Referencing the table is a possibility due to the fact that you could DELETE FROM with an INNER JOIN.
A simple example of deleting from #footbl with an inner join to a different table
DELETE FT
FROM
#DifferentTable DT
INNER JOIN #fooTbl FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Whereas this generates an error due to not specifying what table to delete from
DELETE
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Msg 156, Level 15, State 1, Line 45 Incorrect syntax near the keyword
'AS'.
And this also works
DELETE #fooTbl
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
In short, you need to specify the alias/table to delete from if you are referencing multiple tables, but you don't have to when you only reference the one.
What is the best practice?
In terms of best practice I don't think that it matters much for the single table. Aliasses can be useful as to make adapting / reusing scripts easier.
Some more examples in the Microsoft documentation
D. Using joins and subqueries to data in one table to delete rows in another table
Using the table name twice
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Using an alias
DELETE spqh
FROM
Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Both serving the exact same purpose.
answered 10 hours ago
Randi VertongenRandi Vertongen
6,1882931
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f240135%2fsql-server-delete-syntax%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
What is and what is needed that parameter before the delete keyword
(In my example coincides with table name)?
Referencing the table is a possibility due to the fact that you could DELETE FROM with an INNER JOIN.
A simple example of deleting from #footbl with an inner join to a different table
DELETE FT
FROM
#DifferentTable DT
INNER JOIN #fooTbl FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Whereas this generates an error due to not specifying what table to delete from
DELETE
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Msg 156, Level 15, State 1, Line 45 Incorrect syntax near the keyword
'AS'.
And this also works
DELETE #fooTbl
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
In short, you need to specify the alias/table to delete from if you are referencing multiple tables, but you don't have to when you only reference the one.
What is the best practice?
In terms of best practice I don't think that it matters much for the single table. Aliasses can be useful as to make adapting / reusing scripts easier.
Some more examples in the Microsoft documentation
D. Using joins and subqueries to data in one table to delete rows in another table
Using the table name twice
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Using an alias
DELETE spqh
FROM
Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Both serving the exact same purpose.
answered 10 hours ago
Randi VertongenRandi Vertongen
6,1882931
add a comment |
What is and what is needed that parameter before the delete keyword
(In my example coincides with table name)?
Referencing the table is a possibility due to the fact that you could DELETE FROM with an INNER JOIN.
A simple example of deleting from #footbl with an inner join to a different table
DELETE FT
FROM
#DifferentTable DT
INNER JOIN #fooTbl FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Whereas this generates an error due to not specifying what table to delete from
DELETE
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Msg 156, Level 15, State 1, Line 45 Incorrect syntax near the keyword
'AS'.
And this also works
DELETE #fooTbl
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
In short, you need to specify the alias/table to delete from if you are referencing multiple tables, but you don't have to when you only reference the one.
What is the best practice?
In terms of best practice I don't think that it matters much for the single table. Aliasses can be useful as to make adapting / reusing scripts easier.
Some more examples in the Microsoft documentation
D. Using joins and subqueries to data in one table to delete rows in another table
Using the table name twice
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Using an alias
DELETE spqh
FROM
Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Both serving the exact same purpose.
answered 10 hours ago
Randi VertongenRandi Vertongen
6,1882931
add a comment |
What is and what is needed that parameter before the delete keyword
(In my example coincides with table name)?
Referencing the table is a possibility due to the fact that you could DELETE FROM with an INNER JOIN.
A simple example of deleting from #footbl with an inner join to a different table
DELETE FT
FROM
#DifferentTable DT
INNER JOIN #fooTbl FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Whereas this generates an error due to not specifying what table to delete from
DELETE
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Msg 156, Level 15, State 1, Line 45 Incorrect syntax near the keyword
'AS'.
And this also works
DELETE #fooTbl
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
In short, you need to specify the alias/table to delete from if you are referencing multiple tables, but you don't have to when you only reference the one.
What is the best practice?
In terms of best practice I don't think that it matters much for the single table. Aliasses can be useful as to make adapting / reusing scripts easier.
Some more examples in the Microsoft documentation
D. Using joins and subqueries to data in one table to delete rows in another table
Using the table name twice
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Using an alias
DELETE spqh
FROM
Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Both serving the exact same purpose.
answered 10 hours ago
Randi VertongenRandi Vertongen
6,1882931
What is and what is needed that parameter before the delete keyword
(In my example coincides with table name)?
Referencing the table is a possibility due to the fact that you could DELETE FROM with an INNER JOIN.
A simple example of deleting from #footbl with an inner join to a different table
DELETE FT
FROM
#DifferentTable DT
INNER JOIN #fooTbl FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Whereas this generates an error due to not specifying what table to delete from
DELETE
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
Msg 156, Level 15, State 1, Line 45 Incorrect syntax near the keyword
'AS'.
And this also works
DELETE #fooTbl
FROM #DifferentTable AS DT
INNER JOIN #fooTbl AS FT
ON DT.attr = FT.attr
WHERE FT.attr ='some';
In short, you need to specify the alias/table to delete from if you are referencing multiple tables, but you don't have to when you only reference the one.
What is the best practice?
In terms of best practice I don't think that it matters much for the single table. Aliasses can be useful as to make adapting / reusing scripts easier.
Some more examples in the Microsoft documentation
D. Using joins and subqueries to data in one table to delete rows in another table
Using the table name twice
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Using an alias
DELETE spqh
FROM
Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Both serving the exact same purpose.
answered 10 hours ago
Randi VertongenRandi Vertongen
6,1882931
edited 5 hours ago
answered 10 hours ago
Randi VertongenRandi Vertongen
6,1882931
answered 10 hours ago
Randi VertongenRandi Vertongen
6,1882931
answered 10 hours ago
Randi VertongenRandi Vertongen
6,1882931
6,1882931
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f240135%2fsql-server-delete-syntax%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
