Solved

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
RESTORE A BACKUP IN SQL 2012 from SQL 2008 9 64
SQL Query 2 31
sql server query 18 37
MS SQL and inserting dates into tables using stored procedures 6 15
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

860 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