February 29, 2012 at 7:39 am
DELETE
FROM loadprogress.[PUB].[TC-CARD]
FROM loadprogress.[PUB].[TC-CARD]
JOIN #TCCard_Temp t ON
t.TCNum = [tc-number]
AND t.TCCard = [tc-card]
AND active = 0
Can't say I've ever seen a double FROM clause like this, but it compiles and runs just fine. In fact, removing one of the "FROM loadprogress.[PUB].[TC-CARD]" results in a syntax error
Msg 156, Level 15, State 1, Procedure Load_TC_CARD, Line 128
Incorrect syntax near the keyword 'JOIN'.
I'm tired today and need someone to point out the obvious... :unsure:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 29, 2012 at 7:43 am
It's the syntax used to delete using a subquery.
February 29, 2012 at 7:48 am
I didn't know this could work.
I've always seen things like DELETE sometable FROM sometable, but never ran into "double from".
Nice to know.
-- Gianluca Sartori
February 29, 2012 at 8:00 am
Just to clarify. INSERT has an optional INTO that is almost never specified, and DELETE has an optional FROM that is almost never specified. In both cases, the table name is required, which is why dropping the entire FROM clause produces an error.
So, these two statements are equivalent.
DELETE FROM YourTable;
DELETE YourTable;
But dropping the entire FROM clause is invalid.
DELETE
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 29, 2012 at 8:14 am
To build on the previous answer, if this format is more clear, you can use it instead. They're logically equivalent and it illustrates dropping the from clause.
DELETE T
FROM loadprogress.[PUB].[TC-CARD] T
JOIN #TCCard_Temp t
ON t.TCNum = [tc-number]
AND t.TCCard = [tc-card]
AND active = 0
February 29, 2012 at 8:17 am
drew.allen (2/29/2012)
Just to clarify. INSERT has an optional INTO that is almost never specified
I see it used all the time. Maybe it's just personal coding style/preference.
-- Gianluca Sartori
February 29, 2012 at 8:26 am
I checked with the developer who wrote it and apparently this particular syntax is used to delete duplicates from the table so a unique clustered index can be created. Interesting.
I personally probably would've used ROW OVER or something to determine dups and deleted the records where the count was > 1 but oh well, guess it proves there are so many different ways to skin a cat within SQL :w00t:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 29, 2012 at 8:33 am
It's legal, but I agree with Recurs1on that it's confusing and not clear.
I don't use DELETE FROM (or INSERT INTO), but rather the
DELETE t
from xxxx t
...
February 29, 2012 at 9:55 am
MyDoggieJessie (2/29/2012)
DELETEFROM loadprogress.[PUB].[TC-CARD]
FROM loadprogress.[PUB].[TC-CARD]
JOIN #TCCard_Temp t ON
t.TCNum = [tc-number]
AND t.TCCard = [tc-card]
AND active = 0
Can't say I've ever seen a double FROM clause like this, but it compiles and runs just fine. In fact, removing one of the "FROM loadprogress.[PUB].[TC-CARD]" results in a syntax error
Msg 156, Level 15, State 1, Procedure Load_TC_CARD, Line 128
Incorrect syntax near the keyword 'JOIN'.
I'm tired today and need someone to point out the obvious... :unsure:
I think the problem in the original code above is that the table name in the second FROM clause is not aliased. Have tried it myself, but it is a possibility.
February 29, 2012 at 3:14 pm
CELKO (2/29/2012)
This is an old Sybase syntax which goes back to the pre_ANSI Standards days. You will also see UPDATE.. FROM.. and *= in the code from that era.The code you posted was awful. Embedded dashes from COBOL? Singular table names (another IDEF convention for COBOL file systems)? An assembly language bit flag? In SQL? And a temp table to mimic a COBOL scratch tape temp file? SQL would use a derived table.
Since we have no DDL, all I can do is give you a skeleton:
DELETE FROM TC_cards
WHERE EXISTS
(SELECT *
FROM (<< fake_scratch_tape >>) AS S
WHERE S.tc_nbr = TC_cards.tc_nbr
AND TC_cards.active_flg = 0);
But you can also use a MERGE:
MERGE INTO TC_cards AS T
USING <<fake_scratch_tape>> AS S
ON S.tc_nbr = T.tc_nbr
AND T.active_flg = 0)
WHEN MATCHED
THEN DELETE.
You need to check the rest of the system for other “COBOL-isms” in it.
Once again with the attacks. Rereading what was originally posted I don't see where the OP said he wrote it, but that someone else had and that he had seen this syntax before.
Stop being so arrogant and self righteous. If you want to help, try being helpful and not so rude and condescending to everyone.
Just because you don't like how a system is coded or goes against how you would do it, doesn't mean the people on the other side can necessarily change things. Sometimes systems are what they are and we have to learn to live with and support them. Trust me, I helped support a COBOL system for 11 years that was in desperate need of a rewrite from day one. We didn't have the time or resources to accomplish that rewrite, and that system is still in use today. It does what the users want, and supports a $25 million dollar piece of a business that is mostly profit. It is also the source of all data used to support their web store as well.
February 29, 2012 at 5:41 pm
Well said. Yes I was only sharing with the forum the bizarre code I had come across. Once again, not something I would have written or implementee but was very surprised it would even compile (clearly the compiler thought or was ok). The person who wrote it said they got the method for removing dups off of a google search. It works great and all that but when I first saw it I honestly thought it was a copy and paste error!
I absolutely love this website and hold great respect for all the sharp minds that contribute to it. Always a great place to start learning something new daily...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply