challenging query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

challenging query

Author

Topic

cqldba303
Starting Member

16 Posts

Posted - 2011-10-05 : 16:34:50

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)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-05 : 17:26:38

Is this what you want? BTW, why does the same Student_ID have 3 different names?


declare @yourTable table (Student_ID int, Name varchar(20), Type varchar(20), Product varchar(20))
insert @yourTable
select 10001, 'David', 'Virtual', 'Infra' union all
select 10002, 'Troy', 'Hardware', 'Solution' union all
select 10002, 'Elizabeth', 'Core', 'Server' union all
select 10003, 'Karen', 'Windows', 'Solution' union all
select 10002, 'Tony', 'messaging', 'Software' union all
select 10004, 'Loren', 'Hardware', 'Server' union all
select 10004, 'Katty', 'messaging', 'Solution'

update y set
       type = 'Virtual'
from   (
       select student_id
       from @yourTable
       where  [type] = 'Hardware'
       and    product = 'Solution'
       group by student_id
       ) d
join   @yourTable as y on y.student_id = d.student_id

select * from @yourTable

OUTPUT:

Student_ID  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

Be One with the Optimizer
TG

cqldba303
Starting Member

16 Posts

Posted - 2011-10-05 : 18:57:43

Your query sound answer to my problem. But seem like instead of 'from' it has to be 'where' right ?
well i just created quick table to set example, didn't notice name difference.

cqldba303
Starting Member

16 Posts

Posted - 2011-10-05 : 18:59:46

actually i am trying to run this query in MS access 2010 and it is giving me syntax error.....can you help where can be the problem ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-05 : 21:27:31

quote:
Originally posted by cqldba303

actually i am trying to run this query in MS access 2010 and it is giving me syntax error.....can you help where can be the problem ?

The problem is either
(a) you used the wrong Database. Should be SQL Server 2005

(b) you posted in wrong forum. Should be under MS Access

KH
[spoiler]Time is always against us[/spoiler]

cqldba303
Starting Member

16 Posts

Posted - 2011-10-07 : 14:59:18

well nobody is able to solve even in MS ACCESS OR sql server forum

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-07 : 15:38:51

I did offer a suggestion in this your MS Access thread but you didn't respond. But based on the little bit of googling I did it sounds like the JET engine as a problem with using derived tables with aggregations as part of an UPDATE query. Other folks seemed to need to use two queries (one as the derived table, the other as the update statement). When I tried to set up a small sample access db I was able to make it work by removing the GROUP BY clause from the derived table.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources