Update set count sql

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 10 years, 7 months ago. Active 1 year ago. Viewed 81k times. Ali Ali 7, 20 20 gold badges 99 99 silver badges bronze badges. Why exactly do you want to store that information in your articles table?

Have you considered counting the comments each time you need that information? That way you avoid having duplicate informations in your database schema. Well the articles table is huge and I want to avoid having to do a join because I also need to sort articles based upon how most commented. Ok, then another option for you is some kind of "materialized view".

This seems to be a duplicate of stackoverflow. Add a comment. Active Oldest Votes. In this article, we will learn different methods that are used to update the data in a table with the data of other tables. Mostly, we use constant values to change the data, such as the following structures. The full update statement is used to change the whole table data with the same value.

This type of update statement is a bit complicated than the usual structures. In the following sections, we will learn how to write this type of update query with different methods, but at first, we have to prepare our sample data. With the help of the following query, we will create Persons and AddressList tables and populate them with some synthetic data. These two tables have a relationship through the PersonId column, meaning that, in these two tables, the PersonId column value represents the same person.

In this method, the table to be updated will be joined with the reference secondary table that contains new row values. So that, we can access the matched data of the reference table based on the specified join type. Lastly, the columns to be updated can be matched with referenced columns and the update process changes these column values. After the execution of the update from a select query the output of the Persons table will be as shown below;.

After the SET keyword, we specified the column names to be updated, and also, we matched them with the referenced table columns. After the FROM clause, we retyped the table name, which will be updated. In addition to this, we can specify a WHERE clause and filter any columns of the referenced or updated table. We can also rewrite the query by using aliases for tables. Indexes are very helpful database objects to improve query performance in SQL Server.

Particularly, if we are working on the performance of the update query, we should take into account of this probability. The following execution plan illustrates an execution plan of the previous query. The only difference is that this query updated the 3. This query was completed within 68 seconds. We added a non-clustered index on Persons table before to update and the added index involves the PersonCityName and PersonPostCode columns as the index key.

The following execution plan is demonstrating an execution plan of the same query, but this query was completed within seconds because of the added index, unlike the first one. We have seen this obvious performance difference between the same query because of index usage on the updated columns. Create a free Team What is Teams? Collectives on Stack Overflow.

Learn more. Update count column from data in another table Ask Question. Asked 9 years, 7 months ago. Active 6 years, 6 months ago. Viewed 32k times. Aaron Bertrand k 36 36 gold badges silver badges bronze badges. Radu D Radu D 3, 8 8 gold badges 40 40 silver badges 68 68 bronze badges. I recommend you make every effort to not use cursors when writing SQL, as there will always be a set based way to write what you want against the database.

Of course there will always be an exception to this rule. Add a comment. Active Oldest Votes. Id You might want to test performance for the various ways to do this, if that's important. Talking of performance; I remember reading somewhere that count 1 instead of count id is better not that you'd probably notice it.

Since the id field does need to be selected out as part of the query If you see this stated somewhere other than your memory , please point it out because it should be corrected or clarified. Fair comment - I think I'll have a hard time finding the article again.

Problem with this, is that it will set TotalViews to 0 for everything that doesnt match the select — Dan. Dan you mean where there's no match between Items and ItemViews?