SQL Loop in Query Analyzer? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Loop in Query Analyzer?

Author

Topic

GregLuce
Starting Member

4 Posts

Posted - 2002-11-25 : 16:50:16

I need to populate a field called “companyid” in a table called “tblBusiness” based on the company name that already exists in the table. I know I could do it with a ColdFusion script looping over the distinct company names and incrementing a variable to use for the companyid. But can I just do this sort of thing in query analyzer with a cursor loop or something? I’ve written loops in stored procedures and in Oracle SQL Plus, but have no idea where to start in query analyzer with SQL Server.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-11-25 : 17:47:18

If you post the DDL (CREATE TABLE) statements, and some DML (INSERT STATEMENTS) we can help you do with pretty easily I bet.

I just re-read your post, and I think you want a incrimenting number for company ID right?

Maybe something liek this will do what you want:

 
ALTER TABLE tblBusiness
     ADD companyid int IDENTITY(1,1)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

mdanwerali
Starting Member

30 Posts

Posted - 2002-11-26 : 04:31:03

update tblbusiness set companyid = (select substring(companyname,1,2) )

as u said based on company name, so i didnt in this way....

quote:

I need to populate a field called “companyid” in a table called “tblBusiness” based on the company name that already exists in the table. I know I could do it with a ColdFusion script looping over the distinct company names and incrementing a variable to use for the companyid. But can I just do this sort of thing in query analyzer with a cursor loop or something? I’ve written loops in stored procedures and in Oracle SQL Plus, but have no idea where to start in query analyzer with SQL Server.

GregLuce
Starting Member

4 Posts

Posted - 2002-11-26 : 08:56:50

I didn't explain it properly. I want to populate this new companyid column based on the company name in each record. If there are 5 contact records with company "Publix Super Markets" I want them all to have companyID 1 or whatever. The query would sort by company name so I suppose "AAA" would be companyid 1. :-)

SET companyID = 1
WHERE company = 'Publix Super Markets'

SET companyid = 2
WHERE company = 'NASCAR'

In ColdFusion I could just query the distinct companies and then loop over that resultset and do the updates to companyid incrementing a counter or using the currentrow variable for the id. But how do I run a SQL loop just in Query Analyzer to do the same thing?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-26 : 09:35:57

Greg, what you need is to use an UPDATE statement
.
.
.
.
.
.
.
.
.
.
.
.
.
(oh, you wanted a more detailed answer? re-read the first line in MichaelP's post)

Jay White
{0}

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-26 : 09:41:34

Using case to do updates
http://www.databasejournal.com/features/mssql/article.php/1460001

If you have to do it all the time you might want to create a table with the names and ids and then update based on a join to that table.

GregLuce
Starting Member

4 Posts

Posted - 2002-11-26 : 09:50:38

OK, I'm still not explaining this correctly. Of course I need an UPDATE statement. What I want to do is loop over the distinct company names. Then inside that loop I want to update all the records companyid's to a #. Here's some psuedo-code:

(query1)
SELECT distinct company
FROM tblBusiness
SET counter = 1

LOOP (over query1)
UPDATE tblBusiness
SET companyid = counter
WHERE company = query1.company

SET counter = counter + 1
END LOOP

Again I could write a ColdFusion script that would do this for me, but I'd like to know how to do things like this in Query Analyzer.

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-26 : 10:04:37

There are probably numerous ways to do this.. One would be like this, using temporary tables... (original table is the equivalent of your table, so obviously you wouldn't need to drop or create it).

drop table #moo
drop table #originaltable

create table #moo
(theid int identity,
thename varchar (20))

create table #originaltable
(emptyids int null,
companyname varchar(20) not null)

insert into #originaltable (companyname) values ('companyone')
insert into #originaltable (companyname) values ('companytwo')
insert into #originaltable (companyname) values ('companyone')

insert into #moo (thename) (select distinct companyname from #originaltable)

Select * from #moo

select * from #originaltable

UPDATE #originaltable SET emptyids = theid
FROM #originaltable inner join #moo on #originaltable.companyname = #moo.thename

select * from #originaltable

-------
Moo.

sherrer

64 Posts

Posted - 2002-11-26 : 11:26:10

I think I am following what you need.

You can create a table with an identity column and select distinct companies into that table. Then you can update your original table with the values from your temp table. I am assuming that you have duplicate names, if not MichaelP's fist post should work like a charm.

create table #A
(
ID int NULL,
Company char(50) NOT NULL
)

insert into #A select null, 'A'
insert into #A select null, 'A'
insert into #A select null, 'B'
insert into #A select null, 'A'
insert into #A select null, 'C'
insert into #A select null, 'C'
insert into #A select null, 'C'
insert into #A select null, 'C'

create table #B
(
ID int NOT NULL IDENTITY (1, 1),
Company char(50) NOT NULL
)

Insert Into #B (Company) select distinct Company from #A

Update #A set ID = #B.ID
From #A Inner join #B ON #B.Company = #A.Company

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-26 : 17:21:38

This last solution is definitely what you want - only instead of temp tables, you really do want to store the "company" names and ids table (ie #A) as a persisting table.

Otherwise you're going to have to go through this rigmoral next time you get a new company....and next time you get a new company, because #A has an identity(1,1) you'll be guaranteed to get a nice new company number for your next record in tblBusiness....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Henderson
Starting Member

5 Posts

Posted - 2002-11-26 : 17:51:28

Maybe you should concentrate on using the natural key you already have - the company name. I assume you are using this key (companyid) in another table as well to create a relationship.

You might need to look into constraints to prevent a user from inserting an identical company name, but for a different company, which requires a different key. Maybe it's not a reasonable concern for your application.

sherrer

64 Posts

Posted - 2002-11-26 : 23:05:14

rrb is right, I was just using the temp tables to demonstrate my solution. Again, assuming that you have duplicates. You really need to build a "Company" table to store you unique companies with the company id as the primary key and only have the id as a foreign key in your tblBusiness. If your table is already unique, your best solution is to just create the identity column as the primary key. As far as the last post, you are much better off using an integer for the primary key than to use a long varchar (again assuming that your company name can be fairly large... more than 4 characters). It will make a much better foreign key in other tables.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-27 : 00:05:28

quote:

rrb is right, I was just using the temp tables to demonstrate my solution.

sorry sherrer - I actually assumed as much - just thought I should make it plain to Greg

Cheers &

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-27 : 03:46:14

quote:

I think I am following what you need.

I think I did to. Did I not post the same bit of code as you?

*confusion*

-------
Moo.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-27 : 17:13:27

quote:

Did I not post the same bit of code as you?

Sorry mr mist - I saw sherrer's code at the end, and looked no further.

Hey everybody! Mr mist posted the right code first on this one!!!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

sherrer

64 Posts

Posted - 2002-11-27 : 22:14:30

Sry Moo...

I guess I did not read your code close enough. It is the same. I guess that's a good thing, right? :) I'll be more careful in the future.

Apologies...

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-27 : 22:47:43

me mist -

Just wondering whether sherrer was still formulating a response while you posted yours - so yours wasn't there at the time.

I on the other hand have no excuse.

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-28 : 03:34:33

quote:

Hey everybody! Mr mist posted the right code first on this one!!!

That's better. Now my ego is satisfied. Now if I could just fit my head through the door I could go home.

-------
Moo.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-28 : 17:53:38

Well, you'd have to admit that this site does promote the ego trip- especially for people over 500 posts!!!! (door-bang-head-ouch)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources