Update only the oldest entry in a table with foreign key criteria

Question

I'm working on a forum website.

What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts".

"Threads"

"Posts"

I'm not really sure how to update only the oldest entry in "Posts" where the foreign key in "Posts" match the Id in Threads.

I have never really tried advanced UPDATE statements with subqueries, and it isn't one of my strongest skills yet.

This is what i have tried (which updated ALL rows in "Posts"-table):

    UPDATE Threads 
    SET Title = @ThreadTitle 
    WHERE Id = @Id;

    UPDATE Posts 
    SET Content = @Content 
    WHERE 
        (SELECT TOP 1 FkThreadId FROM Posts 
         WHERE FkThreadId = @Id 
         ORDER BY CreationTime ASC)= @Id 

Also is it possible to update joined table instead of using 2 statements, or is it just better to write 2 seperate statements?

Version:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Answer 1

Yes, your query will update all rows in posts. You have to include a condition that restricts the update to one row. One way to do this:

UPDATE Posts 
SET Content = @Content 
WHERE Id = 
    (SELECT TOP 1 Id 
     FROM Posts 
     WHERE FkThreadId = @Id 
     ORDER BY CreationTime ASC) ;

In SQL-Server, you can't update two tables in one statement, so you have to use two statements. You can put them inside a transaction though, so they are treated as an atom, they either both succeed or fail.