Sql Server delete syntaxRestoring 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?
Please figure out this Pan digital Prince
Is it possible to have 2 different but equal size real number sets that have the same mean and standard deviation?
Fermat's statement about the ancients: How serious was he?
What is the logic behind charging tax _in the form of money_ for owning property when the property does not produce money?
Varying the size of dots in a plot according to information contained in list
Is there a set of positive integers of density 1 which contains no infinite arithmetic progression?
Russian word for a male zebra
If there's something that implicates the president why is there then a national security issue? (John Dowd)
Is it safe to change the harddrive power feature so that it never turns off?
How do we say "within a kilometer radius spherically"?
CircuiTikZ: How to draw contactor coil?
C++ logging library
What does the pair of vertical lines in empirical entropy formula mean?
Why was this person allowed to become Grand Maester?
Amplitude of a crest and trough in a sound wave?
How to avoid typing 'git' at the begining of every Git command
Should I refuse to be named as co-author of a low quality paper?
How can I remove material from this wood beam?
Teaching a class likely meant to inflate the GPA of student athletes
Does the Nuka-Cola bottler actually generate nuka cola?
How do i export activities related to an account with a specific recordtype?
Ability To Change Root User Password (Vulnerability?)
Explanation for the two sentences(preposition)
How can one's career as a reviewer be ended?
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 8 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 8 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 8 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 8 hours ago
s.demuros.demuro
644
asked 8 hours ago
s.demuros.demuro
644
asked 8 hours ago
s.demuros.demuro
644
asked 8 hours ago
s.demuros.demuro
644
asked 8 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 8 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 8 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 8 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 8 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 8 hours ago
Randi VertongenRandi Vertongen
6,1882931
edited 3 hours ago
answered 8 hours ago
Randi VertongenRandi Vertongen
6,1882931
answered 8 hours ago
Randi VertongenRandi Vertongen
6,1882931
answered 8 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
