What SQL query should I perform to get the result set expected?

What SQL query should I perform to get the result set expected, giving the first element of the chain (2) as input data, or any of them ?

table name: changes
+----+---------------+---------------+
| id | new_record_id | old_record_id |
+----+---------------+---------------+
|   1|              4|              2|
|      -- non relevant data --       |
|   6|              7|              4|
|      -- non relevant data --       |
|  11|             13|              7|
|  12|             14|             13|
|      -- non relevant data --       |
|  31|             20|             14|
+----+---------------+---------------+


Result set expected:

+--+
| 2|
| 4|
| 7|
|13|
|14|
|20|
+--+

I know I should consider change my data model, but: What if I cou开发者_如何学Pythonldn't?

Thank you in advance!

Following code should work to get your result

CREATE TABLE #return(val int)
DECLARE @VAL int
SET @VAL = 2

WHILE (SELECT count(*) FROM [changes]
WHERE old_record_id = @VAL) > 0
BEGIN
INSERT INTO #return values(@VAL)

SELECT @VAL = new_record_id FROM [changes]
WHERE old_record_id = @VAL
END
INSERT INTO #return values(@VAL)
SELECT * FROM #return

Since you're vague on the database, here is some good literature:

MySQL

Look around section 7, this goes in to hierarchy and recursive functions
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

MSSQL

Good article on the same thing (you're going to find functions the common place, though MSSQL can use WITH).
http://www.sqlservercurry.com/2009/06/simple-family-tree-query-using.html

PostgreSQL

Same type of article. All these have the same premise, working your way up a database tree from child to parent.
http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=24

if you have data in the middle that you do not want to update then the only solution would be

update table set id=2 where id=4;
update table set id=4 where id=7;
update table set id=7 where id=13;
update table set id=13 where id=14;
update table set id=14 where id=20;

But this update might work if it does the updates in the order they appear which will generally happens if you have the pk of the table set to id.

update table set id=(select min(id) from table b where b.id>table.id)

You can also force this by adding an order by id at the end, and let you see if it allows that.