Hi, Please suggest me to right SQL statement for the below mentioned logic. I have five columns Q25A1,Q25A2,Q25A3,Q25A4 AND HQ_BUNDLE. First four columns can have values between 1 to 13 and fifth column can have only 1 to 4. Now, condition is when all first four columns have same value then HQ_BUNDLE will be 4 similarly when any and only three columns have same value then HQ_BUNDLE = 3. similarly when any and only two columns have same value then HQ_BUNDLE = 2. similarly when all the first four columns have different values row wise then HQ_BUNDLE = 1.
asked Oct 02, 2012 at 10:13 AM in Default
I agree with Thomas that this feels like homework, and I also agree that a computed column is the way to go.
With that said, it is good to have options. You can use an update command to make it so as a one time thing. If your table is such that it is rarely updated but HQ_Bundle is frequently selected, doing the update can be more effecient since a computed column will be frequently recalculated (unless the computed column is indexed, but there are some stringent requirements on whether or not a computed column can be indexed.) Again, I would use a case in the update much like Thomas suggested.
If you want, you could enforce it with a CHECK condition. The advantage is that the check condition only affects performance during write operations, and in most databases read operations are much more common and more time-sensitive than write operations are.
You could also use a series of update statements with where clauses for each possibility instead of using one update with a case, but the only situation I would do that in is if this were really homework and you haven't gotten to the CASE statements in your coursework yet.
answered Oct 02, 2012 at 04:46 PM