Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSSQL - Way to do a subselect within the same table?

Posted on 2009-06-30
5
Medium Priority
?
385 Views
Last Modified: 2012-05-07
This is kinda complicated... Not even sure if it's possible, but, essentially I have one table.  I want to update the Code column for rows with a qid of 4, based on the Resp and timestamp and email columns for rows with a qid of 6.  

So, the table looks like this:

qid | Code | Resp | timestamp | email
6 | 5 | BAC | 2007-08-09 18:56:01.009 | x@x.com
6 | 5 | DPR | 2004-12-12 08:09:44.001 | z@z.com
4 | 7 | 10 | 2007-08-09 18:56:01.009 | x@x.com
4 | 7 | 10 | 2002-01-19 11:52:03.011 | b@b.com
4 | 7 | 13 | 2004-12-12 08:09:44.001 | z@z.com

So, if the rows with a qid of 4 have a timestamp and email that is the same as a row with a qid of 6, I want to update it.  If the Code of the corresponding row with a qid of 6 = BAC, I want to update qid 4 to have a code of 1.  Similarly, if the Code = DPR and the timestamps and emails are the same, I want to update qid 4 to have a code of 3.  

I thought maybe there was some sort of subselect or something?  
But, not sure if it's possible or how to write that query?
0
Comment
Question by:n00b0101
  • 4
5 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 2000 total points
ID: 24746076
Okay, so this should work. I created a test scrit and tested based on the data your provided above. In any case please test thoroughly before applying to production table.
P.

CREATE TABLE #Test (
qid int,
Code int,
Resp  varchar(10),
timestamp Datetime,
email varchar(50)
)
 
INSERT INTO #Test VALUES (6 , 5 , 'BAC' , '2007-08-09 18:56:01.009','x@x.com')
INSERT INTO #Test VALUES (6 , 5 , 'DPR' , '2004-12-12 08:09:44.001','z@z.com')
INSERT INTO #Test VALUES (4 , 7 , '10' , '2007-08-09 18:56:01.009', 'x@x.com')
INSERT INTO #Test VALUES (4 , 7 , '10' , '2002-01-19 11:52:03.011', 'b@b.com')
INSERT INTO #Test VALUES (4 , 7 , '13' , '2004-12-12 08:09:44.001', 'z@z.com')
 
Select * from #Test
 
 
UPDATE #Test
SET   Code = CASE When c.resp = 'BAC' then 1
                  When c.resp = 'DPR' Then 3
                  Else a.Code
		     END
From   #Test a INNER JOIN (
						Select	t.qid, m.resp, m.timestamp, m.email
						from	(
									Select *
									From   #Test
									Where qid = 4
								) as t
								INNER JOIN
								(
									Select *
									From   #Test
									Where qid = 6
								) as m
								ON t.TimeStamp = m.TimeStamp and
								   t.email = m.Email
						) as c
		ON a.TimeStamp = c.TimeStamp and
		   a.Email = C.Email and
           a.qid = c.qid

Open in new window

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24746244
I am not hundred percent sur that I understand what you are looking for but if I did the below queries should solve your problem...

update yourtable
set yourtable.code = '1'
from yourtable
           inner join
                     (
                      select timestamp, email, qid
                      from yourtable
                      where code='BAC' and qid = 6
                      ) B
                on yourtable.timestamp = B.timestamp and yourtable.email = B.email and yourtable.qid = 4

update yourtable
set yourtable.code = '3'
from yourtable
           inner join
                     (
                      select timestamp, email, qid
                      from yourtable
                      where code='DPR' and qid = 6
                      ) B
                on yourtable.timestamp = B.timestamp and yourtable.email = B.email and A.qid = 4

Sorry no time to test..PLease run on test server before trying on production...

HTH
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24746280
More concisely

update yourtable
set yourtable.code =
case
          when B.code = 'BAC' then '1'
          when B.code = 'DPR' then '2'
end
from yourtable
           inner join
                     (
                      select timestamp, email, qid
                      from yourtable
                      where code in ('BAC', 'DPR') and qid = 6
                      ) B
                on yourtable.timestamp = B.timestamp and yourtable.email = B.email and yourtable.qid = 4
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24746373
Ooops realized I made a slight mistake

update yourtable
set yourtable.code =
case
          when B.code = 'BAC' then '1'
          when B.code = 'DPR' then '2'
          else code
end
from yourtable
           inner join
                     (
                      select timestamp, email, qid
                      from yourtable
                      where code in ('BAC', 'DPR') and qid = 6
                      ) B
                on yourtable.timestamp = B.timestamp and yourtable.email = B.email and yourtable.qid = 4
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24746386
Actually it was no mistake, both should work...
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question