Update a rows in one table for each row in other table

Hello all, i am a newbie in SQL and i want to ask for your help in order to do the following update statement.

I have a table tblUsers which holds all the users, one of the fields in their GroupId. In the UserGroup table i have a field "UsersCount" and in that field i want to keep the number of the users in that group. Basically i want everytime i am saving a user to recalculate the users in all usergroups.

I am trying to have something like:
SELECT User_GroupId, count(User_GroupId) as UsersCount FROM tblUsers
WHERE DeletedFlag = 0
GROUP BY User_GroupId

and then:
"FOR EACH" User_GroupId
UPDATE tblUserGroups
SET UserGroup_UserCount = UsersCount
WHERE UserGroup_Id = User_GroupId

Thanks a lot.

0
MikeMinsk
8/15/2006 2:58:24 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
250 Views

Similar Articles

[PageSpeed] 16

Try this oneSmile:

UPDATE tblUserGroups
SET UserGroup_UserCount = UsersCount
FROM tblUsersGroups t join
(SELECT User_GroupId, count(User_GroupId) as UsersCount
FROM tblUsers
WHERE DeletedFlag = 0
GROUP BY User_GroupId) As tmp
ON t.User_GroupId=tmp.User_GroupId


Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
8/16/2006 8:14:08 AM
Iori_Jay:

Try this oneSmile:

UPDATE tblUserGroups
SET UserGroup_UserCount = UsersCount
FROM tblUsersGroups t join
(SELECT User_GroupId, count(User_GroupId) as UsersCount
FROM tblUsers
WHERE DeletedFlag = 0
GROUP BY User_GroupId) As tmp
ON t.User_GroupId=tmp.User_GroupId

Thank you very much, I will try it very soon!

0
MikeMinsk
8/16/2006 4:44:48 PM
It worked! Thanks a lot!
0
MikeMinsk
8/16/2006 11:07:07 PM
Reply:

Similar Artilces:

Update row in Table A from row in Table B
Hello, I have 2 tables.... table A and table B. Ok, In a stored procedure, I pass in an int value @ID I want to get the row from Table B WHERE ID =@ID....and then UPDATE the Row in Table A that has the same ID...with the values from table B. Table A ID | Col1 | Col2 ----------------------- 2 a a Table B ID | Col1 | Col2 ----------------------- 2 b b I would like to update A.Col1 with B.Col1 and A.Col2 with B.Col2 Can someone help me out with this syntax? Thanks a lot. update tableA ...

Stored Procedure that fetch each row of a table and update rows in another table
I am working with the following two tables: Category(NewID,OldID) Link(CategoryID,BusinessID) All fields are of Integer Type. I need to write a stored procedure in sql 2000 which works as follows: Select all the NewID and OldID from the Category Table (SELECT NewID,OldID FROM Category) Then for each rows fetched from last query, execute a update query in the Link table. For Example, Let @NID be the NewID for each rows and @OID be the OldID for each rows. Then the query for each row should be.. UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID Please help me ...

Deleting rows from one table based upon rows in another table
I need to delete rows from table1 based on rows in table2. table1 has 1.5 million rows from which I need to delete 20,000 existing in table2. The table structures are the same and I must exactly match 10 (of the 15) columns in the tables. I also need to limit the number of rows being worked on, in a single transaction, to 500 or so. Hi Mike, try the following: set rowcount 500 DELETE table1 from table1 t1,table2 t2 where t1.col1=t2.col1 and t1.col2=t2.col2 and t1.col3=t2.col3 and ........ t1.col10=t2.col10 HTH. Regards P:S You must re-execute the DML statement above 40 time...

Is having a trigger that inserts a row in Table 'A', when a row in same table is inserted by ADo.Net code?
I want to insert a row for a Global user  in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a s...

Add row to table with table.row.add()
Everytime I click on a button I want to add a tableRow to a table like this:        TextBox TB = new TextBox();                TableCell Cell = new TableCell();        Cell.Controls.Add(TB);        TableRow Row = new TableRow();        Row.Cells.Add(Cell);        table.Rows.Add(Row);It works the first time, but when I click the button again the last r...

How to group table rows into one row?
Hi,I have a table that has several records (like gridView). Each is displayed in a separate page in my report. I want everything to be displayed on one page, basically the same as gridView. How do I do that? I use the dataset which copies the SqlDataSource that is used to display gridView.So, either there's something to do in the Report itself or I have to change the dataSet to manually fit the table design that I want - one row. I don't know how to do it. Please help. ...

Showing table columns from multiple tables in Formview, but insert/update/delete rows of Master table
I have a page on which I have GridView and FomView control. On selection of a row in GridView the row gets displayed in the Item template of the FormVIew. The values are the result of inner join from multiple tables i.e. table Stock,Product and Workshop.  Please let me know how I could insert/update/delete data for the base table i.e. Stock table, but be able to see the column values from the associated tables on the Itemtemplate,Edittemplate and Inserttemplate of the FormView. Thanks,Anita Hi:   Take a look at this tutorial. It's for DetailsView and same ...

Wanting to take info from one SQL table and update to a seperate SQL table
Anyone have an idea of how I can take information from one table and edit it and insert into another SQL table.  I am using stored procedures and would like to continue to do so. Here is a copy of what I have so far:   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="mp_Customers" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </div> <h2><strong><font color="#C00000">Please enter a valid account number:</font></strong><...

How to change the Rows of the one table to another table
I want's to change the layout of my table at run time on the basis of some of my Setting File[I don't want's to use webpart orwebzones]Suppose I have three Tables with number of rows.[In reality Each row will contain some controls]On Page_Preinit I want's to set the rows of one table to another also want's to change order of the rows.How Can I do that ?  <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional/...

Adding table rows to a table in an update panel
I'm trying to add table rows to a table that has already been added to the update panel (ie, in design view). When I run my code, the text changes on the page, but I dont get additional rows, its as if its forgetting about the first row that was already added and simply over writing it... Anyone got any thoughts?  I had a label in there before where I'd reference the label and add text to the existing text, that worked fine, the text that was already there just stayed and the dialogue got longer etc, why is it different with the table rows? Any help would be appreciated..Rega...

Select rows from a table based on multiple rows of another table
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

'Row changed between retrieve and update' updating more than one table
I update more than one table with standard algoritm (at bottom) and I've not problem updating dw but if I delete row, when execute the script then PB display error: 'Row focus changed between retrieve and update' No changes made... delete from second table Why? Thanks. ---------------------------------------------------------------------------- - integer rc string err /* The SELECT statement for the DataWindow is: SELECT department.dept_id, department.dept_name, employee.emp_id, employee.emp_fname, employee.emp_lname FROM department, employe...

Select rows from a table based on multiple rows of another table #4
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Select rows from a table based on multiple rows of another table #2
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. Sorry technical problems! I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. I have a table which holds all of my project info and I have another table which holds discriptive project codes like project type, project category, progect life cycle.So in the pro...

Web resources about - Update a rows in one table for each row in other table - asp.net.sql-datasource

Windows Update - Wikipedia, the free encyclopedia
Windows Update is a service provided by Microsoft that provides updates for the Microsoft Windows operating system and its installed components, ...

Gizmodo.com Updates
Nobody should have to use a pay phone , natural disaster or no natural disaster. So if your gadgets are out of juice after Sandy, here’s a tiny ...

Lifehacker.com Updates
By Whitson Gordon Ever run into a situation where you have multiple wireless networks available, but one is clearly better than the other? WiFi ...

Facebook To Update Mobile Apps Every 4 To 8 Weeks
On the same day that Facebook released a bunch of mobile updates for Apple and Android devices , the social network also announced that it will ...

Product Hunt’s iOS app gets a mammoth update
Product Hunt , the popular online service for up-voting technology products and ideas, has sent out an update for its free iOS application adding ...

Glencore shares halted pending update
Shares in Glencore will be placed in a trading halt on Thursday pending the release of a business update, the HKEx said.

Fallout Shelter update brings pets to the vault
See what's new in the latest update here.

Polling update: Trump widens lead; does 2008 polling give Sanders hope?
REPUBLICANS We’ll start with the GOP, because quite frankly, that’s where the biggest drama lives. Pollster.com GOP primary national polling ...

The Cartographer's Gift update for Halo 5: Guardians launches next week
After announcing it at this year’s Game Awards, 343 Industries has released a whole load of details about the upcoming Cartographer’s Gift update ...

Fallout Shelter Update Brings Pets, Dweller Evictions, Sell All Inventory Options
A major update for Fallout Shelter is rolling out to vault managers through Google Play, bringing with it ways to increase high-yield dweller ...

Resources last updated: 12/10/2015 10:52:42 PM