Solved

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

Posted on 2009-06-30
5
370 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 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