Count Records Updated

[romeogq]

I just simply need to count the number of records updated in a SQL statement, such a...

[Dan_Bracuk]

run a select query before you do the update.

[romeogq]

Thanks Dan for the quick response.

I was hoping there was a way around that. Are you sure there is no way to have the update query return a count of some sort to a variable?

[Newsgroup_User]

> I was hoping there was a way around that. Are you sure there is no way to have the update query return a count of some sort to a variable?

Have you read the documentation for whatever database you're running? This
is a DB question, not a CF question: CF just passes SQL statments to the DB
driver, and - when appropriate - receives recordsets back from the DB
driver. It has no idea what goes on between those two events.

NB: when asking a DB question... it's always good to start by saying which
DB you're using, as the answer is going to be different for any given DB
system.

--
Adam

[romeogq]

Thanks for the reply, but I already understand that. The database is MS-SQL, my fault for not stating... although most probably already understood that by looking at the statement. In addition, there are a lot of intelligent individuals of differing backgrounds on this forum, so I am sure the odds are someone will have an applicable answer.

PS: This is by far not my first post, and I have been a member of this forum since 2002/3.

[dongzky]

there is @@ROWCOUNT. refer to this link:
http://msdn.microsoft.com/en-us/library/ms187316.aspx

by the way, have you tried using query_name.RecordCount?

or maybe the result_parameter_name.RecordCount?

e.g.

<cfquery name="qryUpdateCell" datasource="blah" result="resultqryUpdateCell">
UPDATE myTable
SET myCell = thisData
WHERE myRecords = thisCriteria
</cfquery>
<cfoutput>#resultqryUpdateCell.RecordCount#</cfoutput>

[Dan_Bracuk]

quote:

---

by the way, have you tried using query_name.RecordCount?

or maybe the result_parameter_name.RecordCount?

e.g.

<cfquery name="qryUpdateCell" datasource="blah" result="resultqryUpdateCell">
UPDATE myTable
SET myCell = thisData
WHERE myRecords = thisCriteria
</cfquery>
<cfoutput>#resultqryUpdateCell.RecordCount#</cfoutput>

---

For my own curiousity I tried that with CF 7 and oracle, and got -1 for a query that was going to update 1 record.

[Newsgroup_User]

> The database is MS-SQL,

Which VERSION? you're not helping yourself here.

But if I google for about 30sec I come up with various answers. I searched
on "ms-sql how many rows affected by update query". The very first match
has a solution for SQL Server 2k5 (it might well work for previous versions
too: you might need to investigate that). The third link is from a CF blog
(and it's an interesting blog entry).

Did you try looking on Google?

I don't mean to be obstructive, but with questions like this, it is very
easy to help one's self, rather than asking other people to do the work for
you.


> fault for not stating... although most probably already understood that by
> looking at the statement.

What is it about this statement:

{code}
UPDATE myTable
SET myCell = thisData
WHERE myRecords = thisCriteria
{code}

That one could use to identify which DB you're running on? It looks like
pretty standard ANSI SQL to me. I'm only familiar with Oracle, MS-SQL and
MySQL, but that query is fine for all of those.


> In addition, there are a lot of intelligent
> individuals of differing backgrounds on this forum, so I am sure the odds are
> someone will have an applicable answer.

Sure, but the odds are higher if you ask a question in the best
environment.

I wasn't saying you shouldn't ask here, I was suggesting it wasn't perhaps
the *best* place to ask.


> PS: This is by far not my first post, and I have been a member of this forum
> since 2002/3.

Well done.
(not sure why you mention this).

--
Adam