asked
sql server 2008
Hi experts cud you pls help how to update with below condition. Select * from sor.sor.ou r where exists (SELECT err.[OU_ID], sum(CAST(err.CurrentFlag as int)) FROM [SOR].[sor].[OU] err where r.ou_id = err.OU_ID group by err.OU_ID having sum(CAST(err.CurrentFlag as int)) = 0) and r.EFF_DT = (select max(s.eff_dt) from sor.sor.ou s where r.ou_id = s.ou_id) order by br_num I need to update like this: UPDATE sor.sor.OU SET inserttype='D' and CurrentFlag ='1' `
You need to use the FROM clause in the [UPDATE statement][1]. For example, UPDATE r SET inserttype='D' and CurrentFlag ='1' from sor.sor.ou r where exists (SELECT err.[OU_ID], sum(CAST(err.CurrentFlag as int)) FROM [SOR].[sor].[OU] err where r.ou_id = err.OU_ID group by err.OU_ID having sum(CAST(err.CurrentFlag as int)) = 0) and r.EFF_DT = (select max(s.eff_dt) from sor.sor.ou s where r.ou_id = s.ou_id) Note that I'm referencing the table with the alias from the FROM clause. (Incidentally - I assume from your original code that you're on a case insensitive collation?) [1]:
http://technet.microsoft.com/en-us/library/ms177523.aspx
13 People are following this question.
