Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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

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
n00b0101
Asked:
n00b0101
  • 4
1 Solution
 
pssandhuCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Actually it was no mistake, both should work...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now