How far off is my syntax?

[SPetty1979]

Here is the error that I am getting:
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@animal_colors".
Msg 137, Level 15, State 2, Line 22
Must declare the scalar variable "@animal_colors".

Here is my SQL Statement:
go
declare @animal_colors table (color varchar(50));
declare @i int;
declare @row_count int;

INSERT @animal_colors select distinct description from color

set @i = 0
set @row_count = (select count(color) from @animal_colors)

while @i < @row_count
begin
update animal
set color =
(select top 1 color
from color
where color.description = @animal_colors.color
)
from color
inner join animal on animal.color = color.color
where color.description = @animal_colors.color[@i]
delete from color where color not in (select top 1 color from color
where description = @animal_colors.color[@i]
and description = @animal_colors.color[@i]
set @i = @i +1
end

I'm trying to learn how to write these, and I think I'm pretty close, but can't quite figure out what I am missing.

Thanks
Shawn

 

[r937]

sql server doesn't support arrays like that, as far as i know

what are you trying to accomplish with that proc?

r937.com | rudy.ca

 

[SPetty1979]

r937,

This might make a little more sense. Still getting the errors from above. Basically the statement should get the distinct values from one table, then create a temp table with auto_incremented rows.

Once that is done it will check another table where the column matches the color field of the temp table. Then it will set them equal to the top 1 PK from the original table followed by deleting the other PKs with the same description.

Here is the new script.

go
declare @animal_colors table (row_id int NOT NULL IDENTITY(1,1) ,color varchar(50))
declare @i int
declare @row_count int

INSERT @animal_colors select distinct description from color

set @i = 1
set @row_count = (select max(row_id) from @animal_colors)

while @i < @row_count
begin
update animal
set color =
(select top 1 color
from color
where color.description = @animal_colors.color
and @animal.rowid = @i
)
from color
inner join animal on animal.color = color.color
where color.description = @animal_colors.color and @animal.rowid = @i
delete from color where color not in (select top 1 color from color
where description = @animal_colors.color and @animal.rowid = @i
and description = @animal_colors.color and @animal.rowid = @i
@i = @i + 1
end

 

[r937]

well, i didn't understand your explanation, and i've never worked with DECLARE TABLE, but i know this has to be wrong --

INSERT @animal_colors select distinct description from color

that table has two columns but your SELECT only supplies one

try this --

INSERT @animal_colors ( color )
select distinct description from color

also, i don't understand your subquery

i always figured TOP was useless without an ORDER BY

but then, i don't really understand what you're doing or why

sorry

r937.com | rudy.ca

 

[SPetty1979]

the declare of type table was supposed to be a way to create a temp table instead of using CREATE TABLE and having to drop it at the end. That part works great, same results as doing this:

CREATE TABLE animal_colors(
row_id int NOT NULL IDENTITY(1,1),
color varchar(50)
)
INSERT INTO animal_colors (color)
SELECT
description
FROM
color

However it is not taking in my subquery. It is saying it needs to be declared. Going your route and making an actual table yields me the following errors:

The column prefix 'animal_colors' does not match with a table name or alias name used in the query.

Regarding TOP, I am taking the TOP 1,so it is just 1 result and that is all I am looking at, so no need for a GROUP BY. The main SQL statement works perfect when I replace ANIMAL_COLORS.COLOR with an actual color description. I am literally just trying to put a while loop around it that will run the descriptions through for me.

 

[SPetty1979]

updated script:

CREATE TABLE animal_colors(row_id INT NOT NULL IDENTITY(1,1) ,color VARCHAR(50))
INSERT INTO animal_colors (color)
SELECT
[description]
FROM
color

DECLARE @i INT
DECLARE @row_count INT

SET @i = 1
SET @row_count = (SELECT MAX(row_id) FROM animal_colors)

WHILE @i < @row_count
BEGIN
UPDATE animal
SET color =
(SELECT TOP 1 color
FROM color
WHERE color.[description] = animal_colors.color
and animal_colors.row_id = @i
)
FROM color
INNER JOIN animal ON animal.color = color.color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i

DELETE FROM color WHERE color NOT IN (SELECT TOP 1 color FROM color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i)
AND color.[description] = animal_colors.color AND animal_colors.row_id = @i
SET @i = @i + 1
END
DROP TABLE animal_colors

 

[AlexCuse]

Does that work for you? Your declaration of the table @animal_colors was right, your problem was one of the scope of the table variable.

You can't use table variable in a subquery the way you were, bug I believe you can join to it (or join to a query off of the table variable)

from color
inner join 
(
select color from @animal_color
where row_id = @i
) ac
on color.description = ac.color

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SPetty1979]

I think that has helped alot Alex, however that leaves me with my DELETE statement

DELETE FROM color
WHERE color NOT IN (SELECT TOP 1 color FROM color
INNER JOIN animal_colors on color.description = animal_colors.color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i)
AND color.[description] = animal_colors.color AND animal_colors.row_id = @i

Not 100% familiar on how to inner join animal_colors so that this delete statement will run properly.

Thanks
Shawn

 

[AlexCuse]

I don't really understand what your delete statement is trying to do, but it looks like you just want to delete everything from color that has the same color as a certain row of @animal_colors? If that's right, this should do it for you (not tested, and make sure to save a backup before testing a delete of course)

delete a
from color a
inner join
(
select color from @animal_colors
where row_id = @i
) b
on a.[description] = b.color

If this doesn't get it done, can you post back with exactly what you mean to delete? I still don't fully understand your goal.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SPetty1979]

Thanks for the help Alex. Here's my final statement which seems to be working perfectly.

-- Temporary Table Creation
CREATE TABLE ac (row_id INT NOT NULL IDENTITY(1,1) ,color_desc VARCHAR(150),results int)
INSERT INTO ac (color_desc,results)
select description,count(description) as count
from color
GROUP BY description having count(description) > 1

-- Variable Declarations
DECLARE @i INT
DECLARE @row_count INT
DECLARE @PrintMessage varchar(50)

SET @i = 1
SET @row_count = (SELECT MAX(row_id) FROM ac)

-- Beginning Color Table and Animal Table Consolidation
WHILE @i <= @row_count
begin
SET @PrintMessage = 'Starting Update';
PRINT @PrintMessage;
UPDATE animal
SET color =
(SELECT TOP 1 color
FROM color
INNER JOIN ac ON color.[description] = ac.color_desc
WHERE color.[description] = ac.color_desc AND ac.row_id = @i
)
FROM color
INNER JOIN animal ON animal.color = color.color
INNER JOIN ac ON color.[description] = ac.color_desc
WHERE color.[description] = ac.color_desc AND ac.row_id = @i
SET @PrintMessage = 'Starting Delete'
PRINT @PrintMessage
DELETE
FROM color
WHERE color NOT IN (SELECT TOP 1 color FROM color
INNER JOIN ac ON color.[description] = ac.color_desc
WHERE color.[description] = ac.color_desc AND ac.row_id = @i)
AND color.[description] = (SELECT TOP 1 color.[description] FROM color
INNER JOIN ac ON color.[description] = ac.color_desc
WHERE color.[description] = ac.color_desc AND ac.row_id = @i)
SET @i = @i +1
end
drop table ac