Solved

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

Posted on 2009-06-30
5
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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