2

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"

Threads-table

"Posts"

Posts-table

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)

  • Is this for SQL-Server? – ypercubeᵀᴹ Mar 8 '14 at 2:48
  • It is for Visual Studio 2012 SQL server database (mdf). – chmodder Mar 8 '14 at 2:51
  • Visual studio is not the DBMS but I guess you have SQL-Server 2012 version. What does SELECT @@version; show? – ypercubeᵀᴹ Mar 8 '14 at 2:55
  • 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 <X64> (Build 7601: Service Pack 1) – chmodder Mar 8 '14 at 2:57
4

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.

  • Yes, that solved it. I'm surprised that i was this close. Thanks – chmodder Mar 8 '14 at 2:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.