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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.