Databases
StackOverflow2013
Postsdbo
POSQL UPDATE via INNER JOIN?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL UPDATE via INNER JOIN?
    text
    copied!<p>I had to write a query to update all records in a table based on records that exist in two other different tables. I wrote the following three iterations of the query, I think the third one is the most efficient and the first one the worst. I just wanted a second opinion, and find out if i can do better than the third version below:</p> <p>P.S : The first one is not really a valid SQL query, but a pseudocode of how i planned to query the database.</p> <pre><code>SELECT AccountID,Label FROM QueueTable For each record in query above SELECT FeedbackID FROM FeedbackIndexed WHERE FeedbackIndexed.Label = QueueTable.Label AND FeedbackIndexed.AccountID = QueueTable.AccountID UPDATE FeedbackTable SET Flag = 1 WHERE FeedbackID=@FeedbackID next --------------------------------------------------------------------------------------------------------------------- UPDATE FeedbackTable SET Flag = 1 WHERE FeedbackID IN(SELECT DISTINCT FeedbackID FROM FeedbackIndexed, QueueTable WHERE FeedbackIndexed.Label = QueueTable.Label AND FeedbackIndexed.AccountID = QueueTable.AccountID) ---------------------------------------------------------------------------------------------------------------------- UPDATE FeedbackTable SET FeedbackTable.Flag = 1 FROM FeedbackTable INNER JOIN FeedbackIndexed ON FeedbackIndexed.FeedbackID = FeedbackTable.FeedbackID INNER JOIN QueueTable WITH (TABLOCK) ON FeedbackIndexed.Label = QueueTable.Label AND FeedbackIndexed.AccountID = QueueTable.AccountID (I used table lock on QueueTable because at the end of this query i want to drop all records from the que and don't want other parts of the app adding more records to this table while the query above runs, is that right way to do this?) </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload