There has to be an easier way...

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)

There has to be an easier way...

Author

Topic

corey
Starting Member

13 Posts

Posted - 2004-11-30 : 18:43:49

BEGIN
declare @total as int
declare @counter as int
declare @dup_id as varchar(10)
declare @master_id as varchar(10)
declare @new_seq as int
declare @pic_stamp as datetime
SET @counter = 0
SET @total = (SELECT count(f.dup_id)
FROM sysadm.fixdup f
WHERE f.result = '10' and left(f.dup_id,1) = 'A')
WHILE @counter < @total
begin
SET @dup_id = (SELECT TOP 1 (f1.dup_id)
FROM sysadm.fixdup f1
WHERE f1.result = '10' and left(f1.dup_id,1) = 'A'
ORDER BY f1.update_stamp DESC)
SET @master_id = (SELECT TOP 1 (f2.master_id)
FROM sysadm.fixdup f2
WHERE f2.master_id = f1.master_id and f2.result = '10' and left(f2.dup_id,1) = 'A'
ORDER BY f2.update_stamp DESC)
SET @pic_stamp = (SELECT TOP 1 (p.stamp)
FROM sysadm.picture_id p
WHERE f2.dup_id = f1.dup_id and f1.dup_id = @dup_id
ORDER BY p.stamp DESC)
IF (select count (*)
FROM sysadm.picture_it
WHERE image_id = @master_id) = 0
BEGIN
SET @new_seq = 1
END
ELSE
BEGIN
SELECT @new_seq = MAX(image_seq)
FROM sysadm.picture_it p
WHERE p.image_id = @master_id
SET @new_seq = @new_seq + 1
END
UPDATE sysadm.picture_it
SET image_id = @master_id, image_seq = @new_seq
WHERE image_id = @dup_id and stamp = @pic_stamp
UPDATE sysadm.fixdup
SET result = NULL
WHERE dup_id = @dup_id
SET @counter = @counter + 1
END
END

The updates to the fixdup table work just fine, however, the update fails on the picture_it table if the table contains more than 2 records. If the table only has two records, I know I am updating the correct two because they always have the same stamp. If another record is stored via the front end, 2 records are inserted into the database with different stamps. I want to be sure I am always updating the related records in the database with the same stamp. I have looked at this too long perhaps. People have helped me throughout the day today...hopefully someone will show me my errors or perhaps and easier way.

The error I get know is

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 18:46:53

The table that is referenced by f1 doesn't exist in the query:

(SELECT TOP 1 (f2.master_id)
FROM sysadm.fixdup f2
WHERE f2.master_id = f1.master_id and f2.result = '10' and left(f2.dup_id,1) = 'A'
ORDER BY f2.update_stamp DESC)

Same goes true for the next one too:

(SELECT TOP 1 (p.stamp)
FROM sysadm.picture_id p
WHERE f2.dup_id = f1.dup_id and f1.dup_id = @dup_id
ORDER BY p.stamp DESC)

Tara

corey
Starting Member

13 Posts

Posted - 2004-11-30 : 19:23:00

once again, thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 19:33:15

It's been a while since I had to support it, but are you a developer for the Chameleon application? I seem to remember sysadmin being the object owner and the picture database having this structure.

Tara

corey
Starting Member

13 Posts

Posted - 2004-11-30 : 19:38:56

Wow...funny you should ask. I just started with them 8 months ago. Who else have you supported with Chameleon?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 19:43:12

Ron Saxor and one other guy but his name escapes me. I took over for the DBA stuff when Ron left San Diego County to work for HLP. Back then, their database was on Centura SQLBase on Novell and eventually to NT before I left. It has since moved to SQL Server 2000 but I left before that happened.

Tara

corey
Starting Member

13 Posts

Posted - 2004-11-30 : 19:48:37

Nice to meet you. Thank you being an extra set of eyes for me today...I have had to drop this and get back to it all day only to try and pick up where I left off and seemingly miss ALL the little stuff.

Thanks again! Saw your profile...good to see there are other snowboarding computer geeks out there. ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources