Trouble updating an existing table

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)

Trouble updating an existing table

Author

Topic

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2007-03-14 : 14:36:42

I don't know why, but I'm having a hard time coming up with a way to update an existing data.

The data typically looks similar to this:

ItemKey DisplayOrderID ItemDescription
4748 1 Inventory Scrap Material
4748 2 Inventory Scrap Material
4748 3 Inventory Scrap Material
4748 4 Inventory Scrap Material
4748 5 Inventory Scrap Material
4749 1 Inventory Create New Shop Order
4749 2 Inventory Create New Shop Order
4749 3 Inventory Create New Shop Order
4749 4 Inventory Create New Shop Order
4749 5 Inventory Create New Shop Order
4750 1 Inventory Cycle Count
4750 2 Inventory Cycle Count
4750 3 Inventory Cycle Count
4750 4 Inventory Cycle Count
4750 5 Inventory Cycle Count

The above table is updated from a temporary table with the same structure. The problem is that the DisplayOrderID could be overwritten in the application. How can I effectively make sure that the displayorderID is checked or updated in my update statement?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 14:40:35

You want to update the DisplayOrderID?
I have no idea what you want.

Peter Larsson
Helsingborg, Sweden

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2007-03-15 : 10:33:59

Yes, sorry if I didn't explain myself correctly. I would like to be able to oupdate the displayorderid to increment by 1 for each value in the itemkey. If there is a new group in itemkey, then I need to update displayorderid to start over at 1. Hopefully, this explains my problem more clearly. I am unsure of how to accomplish this task, so any help is appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 10:58:18

[code]-- Prepare sample data
DECLARE @Sample TABLE (ItemKey INT, ItemDescription VARCHAR(32))

INSERT @Sample
SELECT 4748, 'Inventory Scrap Material' UNION ALL
SELECT 4748, 'Inventory Scrap Material' UNION ALL
SELECT 4748, 'Inventory Scrap Material' UNION ALL
SELECT 4748, 'Inventory Scrap Material' UNION ALL
SELECT 4748, 'Inventory Scrap Material' UNION ALL
SELECT 4749, 'Inventory Create New Shop Order' UNION ALL
SELECT 4749, 'Inventory Create New Shop Order' UNION ALL
SELECT 4749, 'Inventory Create New Shop Order' UNION ALL
SELECT 4749, 'Inventory Create New Shop Order' UNION ALL
SELECT 4749, 'Inventory Create New Shop Order' UNION ALL
SELECT 4750, 'Inventory Cycle Count' UNION ALL
SELECT 4750, 'Inventory Cycle Count' UNION ALL
SELECT 4750, 'Inventory Cycle Count' UNION ALL
SELECT 4750, 'Inventory Cycle Count' UNION ALL
SELECT 4750, 'Inventory Cycle Count'

----------------------------------------------------------------------------------------------
-- For a large set of data (mote than 255 records)
----------------------------------------------------------------------------------------------
DECLARE @Stage TABLE (RecID INT IDENTITY(1, 1), ItemKey INT, DisplayOrderID INT, ItemDescription VARCHAR(32))

INSERT @Stage (ItemKey, ItemDescription)
SELECT ItemKey,
ItemDescription
FROM @Sample
ORDER BY ItemKey

UPDATE s
SET s.DisplayOrderID = (SELECT COUNT(*) FROM @Stage AS x WHERE x.ItemKey < s.ItemKey)
FROM @Stage AS s

SELECT ItemKey,
RecID - DisplayOrderID AS DisplayOrderID,
ItemDescription
FROM @Stage
ORDER BY ItemKey,
2

----------------------------------------------------------------------------------------------
-- For a small set of data (less than 256 records)
----------------------------------------------------------------------------------------------
SELECT x.ItemKey,
v.Number AS DisplayOrderID,
x.ItemDescription
FROM (
SELECT ItemKey,
ItemDescription,
COUNT(*) AS cnt
FROM @Sample
GROUP BY ItemKey,
ItemDescription
) AS x
INNER JOIN (
SELECT Number
FROM master..spt_values
WHERE Name IS NULL
AND Number BETWEEN 1 AND 255
) AS v ON v.Number <= x.cnt
ORDER BY 1,
2[/code]

Peter Larsson
Helsingborg, Sweden

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2007-03-15 : 14:54:44

Thank you very much, Peter. I was able to use your code with just a slight modification.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources