Second FROM in T-SQL DELETE

Question

Second FROM in T-SQL DELETE

Paul_J

Hall of Fame

Points: 3698
More actions
April 25, 2006 at 7:43 pm

#152936

Hi all,
I have a query that uses the optional T-SQL extension second FROM word in an sql query:
delete from table1 FROM table2
inner join longname defs on defs.defid = table2.defid
inner join table1 dm on dm.id = defs.id
WHERE
table2.col1 ='whatever'
Now this works fine on one machine with SQL Server installed but it doesn't on another. It throws some SQL error (sorry, I can't remember the exact error, and don't have access to the machine right now) on the 2nd machine.
Anyone know why or what could be causing this?
Cheers,
Paul

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Have you tried using a subquery instead ?!

In all probability your 2nd machine isn't compatible with the t-sql extension - there is a compliance testing level called "fips_flagger" (check it out in BOL) that you can "set" to "off" & then run your t-sql again to see if that removes the error!!!

**ASCII stupid question, get a stupid ANSI !!!**

Paul_J Hall of Fame Points: 3698 More actions · Answer 2

Yeah I've got the subquery equivalent but was just curious to know why it doesn't work. It works on all our dev boxes, but not in production...

Thanks for the info, I'll look up the FIPS_FLAGGER thingy.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

I don't think the From clause is the reason for the error, it may be the where clause because technically a JOIN with a where clause is obsolete since 2003. You are supposed to use the AND operator for filter, SQL unlike other languages compile old versions still in use. Check out SQL performance for details about syntax changes in SQL. Hope this helps.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Paul_J Hall of Fame Points: 3698 More actions · Answer 4

We're actually using SQL Server 2000, so shouldn't the WHERE clause work?

We are using Windows Server 2003 though, but I don't think that makes any difference... hopefully.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Try to avoid "optional" things.

They may not work on every server.

delete dm

from table2

inner join longname defs on defs.defid = table2.defid

inner join table1 dm on dm.id = defs.id

WHERE table2.col1 ='whatever'

_____________
Code for TallyGenerator