Yeah, that's the ticket! I'll look in Books Online for the close and
deallocate cursor syntax. (It took 5 hours for this post to go up!) Thanks
Mark.
DECLARE @company varchar(20) -- or whatever
DECLARE myCursor FOR (select distinct company from tblBusiness where listnum
= 1)
open myCursor
Fetch Next from myCursor INTO @company
UPDATE tblBusiness
SET companyid = (select max(companyid) from tblBusiness
where listnum = 1) + 1
WHERE company = '@company'
close and deallocate the cursor...
-----Original Message-----
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 26, 2002 4:06 PM
To: CF-Talk
Subject: RE: SQL Question
If you are trying to increment - an int for each company, then you can do it
like this:
UPDATE tblBusiness
SET companyid = (select max(companyid) from tblBusiness) + 1
WHERE company = 'blah'
If you must loop through all the companies you will have to open a cursor...
There are lots of examples in sql books on-line... If you need a simple
example I will send you one, but the basic idea is:
DECLARE @company varchar(20) -- or whatever
DECLARE myCursor FOR (select distinct company from tblBusiness)
open myCursor
Fetch Next from myCursor INTO @company
Run your update code
close and deallocate the cursor...
there's a lot more code to it - looping in SQL is a bit more verbose, but
it's not hard to get a handle on it if you want to. Very useful too.
-Mark
-----Original Message-----
From: Luce, Greg [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 26, 2002 9:30 AM
To: CF-Talk
Subject: SQL Question
OK, I tried asking this on SQL Team and nobody understood. I want to
populate a new column in my tblBusiness called companyid. If there are 5
records for "Publix" I want all 5 to have the same companyid. I could do it
using CF very easily like this:
<cfquery name="Q1" datasource="FL100">
SELECT distinct company AS company
FROM tblBusiness
</cfquery>
<cfoutput query="Q1">
<cfquery name="Q2">
UPDATE tblBusiness
SET companyid = #currentrow#
WHERE company = '#company#'
</cfquery>
</cfoutput>
My question is how ccan I do this in query analyzer. I'm sure you can create
loops and variables in an SQL script, I just don't know how.
***Sterling Financial Investment Group, Inc. (SFIG) is a member of
NASD/MSRB/NFA/SIPC. Email transmissions may be monitored. SFIG cannot
accept orders to buy or sell via email. Please visit www.mysterling.com for
more information.***
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
