0

i have a table which contains 10000 records, now i have added the field for placing the plaintext password, so now i want to update all the records, but should update the new field on the basis of the username... like below.

update users set secdetail = case when username = 'abbas' then 'abbas@123' .... end

i am running this query from .net code, using sqlcommand, whenever i try to refresh the pagem after loading for sometime, i gets a message saying, 

The query processor ran out of stack space during query optimization. Please simplify the query

Please help.

Abbas
  • 4,948
  • 31
  • 95
  • 161
  • The 'case' (as command) is not in the logic of the sql, is just here to help for few cases, but not for updating 10k records use in it. Follow what Greg suggest. – Aristos Nov 09 '11 at 01:51

1 Answers1

1

Why not create a loop in your .net code to produce proper sql update statements separated by semicolins

Update Users Set Secdetail = 'abbas@123' Where Username = 'abbas';
Update Users Set Secdetail = 'bbbas@123' Where Username = 'bbbas';
Update Users Set Secdetail = 'cbbas@123' Where Username = 'cbbas';
...
Greg
  • 8,574
  • 21
  • 67
  • 109
  • [This](http://stackoverflow.com/questions/6016475/what-is-better-dynamic-sql-or-where-case/6016488#6016488) says you shouldn't use case statements for large datasets – Greg Nov 08 '11 at 23:16