Need update statement with conditional operator | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need update statement with conditional operator

Hello All,
Suppose there is a table with the following data Table Name : Door
———————-
Door_Id Door_Status
========================
1 0
2 0
3 1
4 1
5 1
6 0
7 1
8 1
9 0
10 1 —



Question:
===============
We have to update the table such a way if Door_id = 1 it can update all the record. For door_id =1 it can update all the records
For door_id =2 it can update record no: 2,4,6,8,10 the records
For door_id =3 it can update record no: 3,6,9 the records
For door_id =4 it can update record no: 4,8 the records
For door_id =5 it can update record no: 5,10 the records
For door_id =6-10 it can update only one record which is equivalent to corresponding
record no. so for example if door_id=2 I need to update row no 2,4,6,8 & 10 for door_status.
if door_status is 0 make it to 1 or if it is 1 make it 0. How to do that in a single update statement?? Need all of your utmost help. Regards Niladri
[Comment withdrawn – Adriaan]
Well, I would like to drop it. But I’ll wait for others Moderators oppinions.
Refhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14085 Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
s_niladri, what did you try?<br />Look for case in sql server help file<br />Also dont post the same question in different forums using different titles [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
I must add, I am disappointed with microsoft interview questions.
Not only in Microsoft, also in other companies most of the interview questions seem like that. Most of them are based on Triggers, Cursors, etc for the beginners [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
My problems with such question is that they don’t reflect real problems you have to solve, they are tricky made-up problems.
Like when the question is "how to improve query" when the answer is to change the data structure.
I didn’t like that question either, but now I think I will use the same question to test understanding of indexes. People usually read about sargs and indexes and remember that index is useful if query contains starting index columns, but quite often they don’t understand why. Based on answer of that question you can see if one really understands how indexes work or not.
I’m not sure how an index will help with the question of "how to improve query". If I’m reading the question and the desire correctly wouldn’t you have to use "character_field like ‘%value%’ or something, and in case of LIKE with starting wildcard, I thought the optimizer can’t use indexes anyway. I was thinking that turning on FULLTEXT "stuff" would do the trick, but still not sure. Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.
The correct answer (IMO) besides changing the table structure is that adding an index would improve a query on specific server that was the first element in the tree. In that case column like @server + ‘%’ would be used.
However if the question was really "how to improve the query" instead of "how to improve the query performance", then the question was missleading at least.
\serverlocation codePathNameDate ReceivedTime ReceivedFileName Now to find out the following query with select statement ,lot of string function need to be use to get the results. Supoose there are million of million rows in this table. So if we follow the above approach it will become very slow. So how to optimize the query to get results in a mimimum time…. qry:
1>List all the files name for a particular Date?
2>List all the files name for a particular location?
3>List all the files name for a particular Server?
4>List all the files name for a particular Path? So the only one that could be sped up would be the SERVER query since it would the first part of the query. How about INDEXED views where there would be a different VIEW for the DATE, LOCATION and PATH aspects of the data? Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.
Yes indexed view would solve the problem, but it doesn’t make sense. Why don’t we just change table structure instead of creating indexed view to mitigate poor table design? I think I mentioned indexed view in a original thread.
quote:Without changing table structure or creating indexed view splitting varchar into multiple columns you can’t do much to optimize the query. You can add an index to improve performance of query 3 and that’s all.
Another possibility is to create computed columns and index them, but this is still changing table structure. Computed columns wouldn’t be materialized in table row but they would be materialized in indexes.
Actually the question as formulated doesn’t make sense, however we don’t know if it was originaly formulated like that.
If the question is related to get an answer for the interview questions, then please refrain posting such questions. As mentioned the user must get the answer using their skills in SQL Server and not from forums. Apart from that topic for the other discussion in this thread, please continue in the other post. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>