How to Delete using INNER JOIN with SQL Server? I want to delete using INNER JOIN in SQL Server 2008. But I get this error,          Msg 156, Level 15, State 1, Line 15         ncorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' AND Date = '2013-05-06' Answer : You need to specify what table you are deleting from, here is a version with an alias: DELETE w

INNER JOIN ON vs WHERE clause For simplicity, assume all relevant fields are NOT NULL. You can do: SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else: SELECT table1.this, table2.that, table2.somethingelse FROM table1 INNER JOIN table2 ON table1.foreignkey = table2.primarykey WHERE (some other conditions) Do these two work on the same way in MySQL? Answer : INNER JOIN is ANSI syntax which you should use. It is generally considered more readable, especially when you join lots of tables.

When should I use Cross Apply over Inner Join? What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I also know that CROSS APPLY doesn't require a UDF as the right-table. In most INNER JOIN queries (one-to-many relationships), I could rewrite them to use CROSS APPLY, but they always give me equivalent execution plans. Can anyone give me a good example

Best way to get identity of inserted row? What is the best way to get identity of inserted row? I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each. Can someone please explain the differences and when I should be using each? Answer: @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

How can I do an UPDATE statement with JOIN in SQL? I need to update this table in SQL Server 2005 with data from its 'parent' table, see below: sale id (int) udid (int) assid (int) ud id (int) assid (int) sale.assid contains the correct value to update ud.assid. What query will do this? I'm thinking a join but I'm not sure if it's possible. Answer : It very much depends on which SQL DBMS you're using. Here are some ways to do it in