what is the wrong with this update

[zircon06]

UPDATE studentmaster
SET active = 0
FROM studentdetail
WHERE studentmaster .orderset_sys = studentdetail.orderset_sys
AND order_text like '%continuation of class%'and class_ID= ' 400000'

Thanks in advance

 

[ESquared]

Aside from the space in "studentmaster .orderset_sys" I don't see anything wrong, but I never ever use that syntax. This is the syntax I use:

SELECT studentmaster, *
--UPDATE studentmaster SET active = 0 
FROM
   studentdetail 
   INNER JOIN studentmaster ON studentmaster.orderset_sys = studentdetail.orderset_sys
WHERE order_text like '%continuation of class%'and class_ID= ' 400000'

Note you can run it as a select just as it is, then when you want it to be an update after validation you select just starting with the word UPDATE to the end of the query.

When you're really good and ready, you can rip out the SELECT statement (or swap them so the SELECT becomes hidden behind the comment).

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bborissov]

There is no JOIN in your update statement, This statement should be:

UPDATE studentmaster
   SET active = 0
  FROM studentdetail, studentmaster
 WHERE studentmaster.orderset_sys =
       studentdetail.orderset_sys
   AND order_text like  '%continuation of class%'and class_ID= ' 400000'

But I prefer ESquared version with INNER JOIN.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP