Any SQL gurus? Weird issue not triggering an error

[spudboy]

I had a typo in some SQL, and instead of triggering an error, I mistakenly processed hundreds of thousands of records to the wrong state in the blink of an eye!

This is the SQL that worked very nicely when it shouldn't have:

Update Contact Set State='NSW'
from ContactAltAddr inner join W on ContactAltAddr.State=W.Code

The first line should have had the table name as ContactAltAddr (Contact is a valid table too, just not part of this operation!!)

I would have expected a compile error. Ran quite happily and updated every single person in the Contact table to be in NSW :(

Any thoughts on why this is valid and didn't trigger a syntax error?

[mikehzz]

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?

[spudboy]

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.

[mikehzz]

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

[onesilop]

check the MSDN resource on update (http://msdn.microsoft.com/en-us/library/aa260662(v=sql.80).aspx)

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.

[dbs]

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.

[George130]

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.

[dbs]

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.
;-)

[horaceOz]

Yes that is a valid statement and please ALWAYS use begin trans, rollback etc as discribed by others when making any changes to a table.
Use your select statement to verify the number of records that will be changed then run your begin trans/update/rollback and check that your record count matches

Also if possible take a backup and restore your database as a test instance and do all updates there first.