question

sandeepiii avatar image
sandeepiii 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' `
sql-server-2008-r2tsql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Dave_Green avatar image
Dave_Green answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks its fine .
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.