Scripting Issue - 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

General SQL Server Forums

New to SQL Server Programming

Scripting Issue

Author

Topic

Spetty
Starting Member

25 Posts

Posted - 2007-03-17 : 11:32:24

Does anyone have any insight as to what is wrong with my SQL statement? All tables exist in this DB however, I get the messages at the bottom.

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

Result:
Server: Msg 107, Level 16, State 2, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.

Kristen
Test

22859 Posts

Posted - 2007-03-17 : 11:55:09

[code]
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
[/code]
animal_colors table is referenced, but not defined in this statement.

Kristen

Spetty
Starting Member

25 Posts

Posted - 2007-03-17 : 12:55:23

I think that has helped alot Kristen, Thanks! Although now my DELETE statement isn't happy and i'm not quite sure how I need to put this guy together.

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

Kristen
Test

22859 Posts

Posted - 2007-03-17 : 13:10:22

FWIW I never write my DELETE / UPDATE statements like that, because I think it leads to the possibility of using a cartesian Join. I use an Alias for the target table - which then allows my target table to be the FROM or any of the JOINs. e.g.


UPDATE U
SET MyCol = '123'
FROM  MyTable AS U
      JOIN MyOtherTable AS O
          ON O.MyPK = U.MyPK
WHERE     U.MyCol = 'FOO'
      AND O.MyCol = 'BAR'

DELETE D
FROM  MyTable AS D
      JOIN MyOtherTable AS O
          ON O.MyPK = D.MyPK
WHERE     D.MyCol = 'FOO'
      AND O.MyCol = 'BAR'

Kristen

Spetty
Starting Member

25 Posts

Posted - 2007-03-19 : 12:22:14

Thanks for the info Kristen. I will definately look into starting to do that as I get better at writing these scripts (we usually don't need much of that in my dept, so I'm still learning).

Here is my final script, seems to be working quite well.

-- 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 12:36:17

You could try to narrow complete code down to something like this

UPDATE		a
SET		a.Color = x.Color
FROM		Animal AS a
INNER JOIN	(
			SELECT		[Description],
					MAX(Color) AS Color
			FROM		Color
			GROUP BY	[Description]
		) AS x ON x.[Description] = a.[Description]

DELETE		c
FROM		Color AS c
LEFT JOIN	Animals AS a ON a.Color = c.Color
WHERE		a.Color IS NULL

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources