Sql-server – Update only the oldest entry in a table with foreign key criteria

join;sql serversql-server-2012subqueryupdate

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)

Best Answer

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.

Related Solutions

Sql-server – Central Management Server – can it manage itself

I had also previously assumed that this wasn't possible at all, until I started playing around with it.

From what I can tell, the matching is only done by the server network name that's entered.

If you attempt to add a server to the tree with the same network name, it won't allow it; however, if you use a different network name, even though it may point to the same instance, it does work.

For example, I successfully registered my local instance .\SQL2008R2DEV as a CMS, and then successfully registered (local)\SQL2008R2DEV in the tree. (Tested in 2012 Management Studio.)

From a management perspective, this may or may not be a good idea. Furthermore, if Microsoft decides to tighten down the policy, any reliance on this ability will force you to change your process.

Certainly if SQL1 would be a production instance in your case, this is a bad idea because you want to separate management duties from serving user data duties. I strongly recommend using a separate instance of SQL Server just for CMS purposes, particularly in a production environment.

Sql-server – blocking ‘SELECT… WITH(NOLOCK)’

If the rebuild is not specified as ONLINE (which you can't do except on Enterprise Edition), the SELECT is blocked because of LCK_M_SCH_S, and if you execute sp_lock you will see it is an exclusive lock. Even NOLOCK can't penetrate that. You can simulate this by:

In one window, start a transaction which rebuilds offline (this is so that you can investigate beyond the rebuild instead of trying to force a really long rebuild). Make note of the spid:
```
USE AdventureWorks2012;
SELECT @@SPID;
BEGIN TRANSACTION;
ALTER TABLE Sales.SalesOrderHeader REBUILD WITH (ONLINE = OFF);
```
In a second window, execute your NOLOCK query (again, make note of the spid):
```
SELECT @@SPID;
SELECT * FROM Sales.SalesOrderHeader WITH (NOLOCK);
```

In a third window, check:

SELECT session_id, blocking_session_id, last_wait_type 
  FROM sys.dm_exec_requests 
  WHERE blocking_session_id IN (two spids above)
     OR session_id IN (two spids above);

EXEC sp_lock;

Scan the second resultset for all of the rows involved with the spid that is the main blocker.

Don't forget to rollback or commit...

Best Answer

Related Solutions

Sql-server – Central Management Server – can it manage itself

Sql-server – blocking ‘SELECT… WITH(NOLOCK)’

Related Question