MSSQL - Way to do a subselect within the same table?
Posted on 2009-06-30
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 | firstname.lastname@example.org
6 | 5 | DPR | 2004-12-12 08:09:44.001 | email@example.com
4 | 7 | 10 | 2007-08-09 18:56:01.009 | firstname.lastname@example.org
4 | 7 | 10 | 2002-01-19 11:52:03.011 | email@example.com
4 | 7 | 13 | 2004-12-12 08:09:44.001 | firstname.lastname@example.org
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?