SQL Server Performance

Challenging Query

Discussion in 'SQL Server 2005 General DBA Questions' started by cqlguy, Oct 5, 2011.

  1. cqlguy New Member

    If you can't see table clearly see the attachment:
    I am facing interesting challenge and wondering if someone can help me with this:

    This is the table I have:
    Student_ID Student Name Type Product
    10001 David Virtual Infra
    10002 Troy Hardware Solution
    10002 Elizabeth Core Server
    10003 Karen Windows Solution
    10002 Tony messaging Software
    10004 Loren Hardware Server
    10004 Katty messaging Solution

    so what I want to do is:
    I want to look for records which has (Product='Solution' and Type='Hardware') and I want to change all the Type of those records to 'Virtual'. But not only that, the challenging part is, I want to change Type of all the records which has (Product='Solution' and Type='Hardware'), and Student_ID is same.

    So for example: In the above table data I want to write a query which will look for 'Solution' in the column Product, if it finds it, it will look for 'Hardware' in the column Type, if it finds it, it will look for similar Student_ID as whatever it was for that particular records in the rest of the table and for all those records it will change Type='Virtual'. (remember if it finds 'Hardware' in the type, and after that when it will be looking for same Student_id in rest of the table, there might be several records where Student_ID will be matching to the same Student_ID of the same records but they might have many different Type - in above example Student_ID 10002 has 3 different Type)

    so if i run that query in the above table it should give me this results:
    Student_ID Student Name Type Product
    10001 David Virtual Infra
    10002 Troy Virtual Solution
    10002 Elizabeth Virtual Server
    10003 Karen Windows Solution
    10002 Tony Virtual Software
    10004 Loren Hardware Server
    10004 Katty messaging Solution

    notice that in result Type didn't change for Student_ID=10004 because even though we did find Product='Solution' we didn't find where it has 'Hardware' for that product.


    thanks (cqldba@gmail.com)
    If you can't see table clearly see the attachment:

    Attached Files:

  2. satya Moderator

    Dont get us wrong, is this a classroom or inteview based question?
  3. MichaelB Member

    I do think so Satya...
    I dont like to help people cheat either! not that its the situation here.. but looks like it
  4. t.rajesh.be New Member

    update [Table_Name] set Type='Virtual' where Student_ID in(select Student_ID from [Table_Name] where Type='Hardware' and product='solution');

    I think this will help u........

Share This Page