Thread: Any SQL gurus? Weird issue not triggering an error

You shouldn't have a from clause in an update statement, a where clause is normal. The first line is a valid statement and was executed. The second line is rubbish and should have thrown some sort of error however a lot of compilers will just drop it with no warnings. It depends on the environment that you are working in. MySQL, php or perl do strange things every now and then because they predominantly work in a web server environment. How were you executing the statement?

Executing in MS SQL Server 2008, but fails equally happily in MS SQL Server 2005. I have tried from both my application (.NET - Visual Studio) and directly executing it from the SQL Management Studio.

I am using the join as the "Where"....

If I fix the first line to be the right table name it works as expected and just a few of the records get updated as I want. It was more that I thought the compiler should have triggered an error, but didn't, so it must be a valid syntax somehow. If it is re-worked to a select statement it fails (as I would expect) because the Contact table is not referenced in the join clause.

That does surprise me too because Microsoft is a bit more strict on that sort of thing. Therefore the SQL compiler has no hesitation in executing the first part of the statement and then joining the tables in the second part for no reason at all. Interesting. I would write that statement differently especially now that you have pointed this out.

Something like-
update (select ContactAltAddr.State from ContactAltAddr inner join W on ContactAltAddr.State=W.Code) as U set U.State='NSW'

I prefer to get my records together before the set happens. Cheers

check the MSDN resource on update

From is valid in an update statement
usually you would use it something like

update Table1
set table1.field1 = table2.field1
from table2 where table2.field2 = "this is not a test"

whenever I am writing update or deletes I always wrap it in transactions

begin transaction

<< update or delete statement>>

select to confirm previous statement

roll back transaction

that way you can validate your statement without screwing up data.

Is it possible that you had the first line highlighted when you ran it? This would explain ms sql running your query successfully as selected area is all that's considered

nope ignore that,... just realised that w is a valid table name and therefore contextually correct. As the from clause has no reference to the update clause there is no filter applicable and the result is all rows are updated by the set clause.

Does MS SQL have a seperate commit?
We use PLSQL so these sorts of things have to then be committed. Helps to stop the bestakes.

ms sql will commit by default. Best practice is to use:

begin transaction

sql script

commit transaction / rollback transaction

sql has a sepearate table that holds the information in a... before update / after update form until it is committed or rolled back.
;-)