Help me break my cursor dependency

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

Transact-SQL (2000)

Help me break my cursor dependency

Author

Topic

maquaro
Starting Member

6 Posts

Posted - 2005-03-30 : 11:33:28

Oh SQL gurus,

I have read the wisdom to stay away from cursors. I also see now why I rely so much upon them. I think row-based because I was an AS400/RPG programmer for 4 years as my first job. Now I understand I need to have a set-based mindset when solving a problem.

I'm this close to breaking free of the cursor imp, but I need suggestions from the SQL Council of Wisdom.

Something I am doing now and want to change:
</start code>
DECLARE Persons TABLE (
Name varchar(50)
Gender char(1)
)

DECLARE Person_Colors (
Name varchar(50)
Color varchar(20)
)

DECLARE tmp_delete (
Name varchar(50)
Color varchar(20)
)

INSERT INTO tmp_delete
SELECT Person.Name, Person_Colors.Color
FROM Persons
INNER JOIN Person_Colors ON Persons.Name = Person_Colors.Name

/* I want to delete every row in tmp_delete from Person_Colors */
DELETE FROM Person_Colors
WHERE Name = tmp_delete.Name
AND Color = tmp_delete.Color

</end code>

How can I do this without a cursor? Again I am set in my row thinking ways and am trying break habits.

Thanks in advance,

maquaro
The Noob on the forum block

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-30 : 12:09:39

DELETE Person_Colors
FROM Person_Colors
INNER JOIN tmp_Delete
ON Person_Colors.Name = tmp_delete.Name
AND Person_Colors.Color = tmp_delete.Color

Corey