January 15, 2007 at 8:10 am
(no, I know it doesn't make much sense...)
I've got a table that records updates made elsewhere, where much of the data gets duplicated, since it might well be updated several times.
I need to get the 'updated counter' value for each unique itemid incremented correct, so the data can then generate some useful output.
currently, all the counters are at 0, with a unique 'updated date' to distinguish them.
I've tried a straight-forward 'find duplicates' query that works fine for deleting data, but can't get it working for an update query:
a sample from the table:
tableID itemID menuID updateNo updatePub updateType updateDate
1 3754 0 1 3091 1 2005-10-04 10:08:48.563
2 3754 0 1 3091 1 2005-10-04 10:11:41.957
3 5161 0 1 1156 1 2005-10-04 12:42:12.060
4 1097 0 1 4134 1 2005-11-03 16:07:27.447
5 1097 0 1 4134 2 2005-11-03 16:08:21.293
6 6137 0 1 579 1 2005-11-04 15:50:00.857
7 6291 0 1 2863 1 2005-11-09 14:26:02.200
this is what I've got so far:
update [navigate_update_history]
-- h1
set updateno = 2 where itemid in(
SELECT h1.[tableID]--, h1.[itemID], h1.[menuID], h1.[updateNo], h1.[updatePub],
--h1.[updateType],
-- h1.[updateDate]
from [navigate_update_history] h1
join [navigate_update_history] h2 on h1.itemid = h2.itemid and h1.updateno = h2.updateno
where h1.[updateno] = 1 --[itemid] in (select navigateid from #countupdates )--where total = 2)
--and h1.updateno > 0
and h1.updatedate > h2.updatedate)
--order by h1.[itemid], h1.updateno, h1.updatedate
January 15, 2007 at 8:53 am
I have some problems to understand what you want to do... I'll try to describe how I got it.
You have a log table of updates, that contains updateDate. There are no duplicities (if you include updateDate in the result), but there can be many rows for each "item" (whatever that means). There is a column "updateNo" in this table, which has incorrect values at the moment, and you want to set these values.
Is that it?
If so, a hint: to get value for updateNo, you need to count, how many updates were done earlier than this one.
CREATE TABLE #mytable (pk int, updateno int, updatedate datetime)
INSERT INTO #mytable
SELECT 1, 0, '20060102'
UNION
SELECT 1, 0, '20060412'
UNION
SELECT 1, 0, '20061225'
UNION
SELECT 2, 0, '20060310'
UNION
SELECT 2, 0, '20060610'
UPDATE a
SET updateNo = (select count(*)+1 from #mytable where a.pk=#mytable.pk and a.updatedate > #mytable.updatedate)
FROM #mytable a
SELECT * FROM #mytable
-- DROP TABLE #mytable
Once you explain the situation in more detail, I'm sure we can arrive at a better solution - this is just something to start with (and maybe it would be even good enough if it is a task that you will only do once).
It is also a question, whether it is a good idea to calculate and write the number of updates into the row... that depends on how you will use it and what situations can occur. In some scenarios, it would be better to calculate the number of updates dynamically.
January 16, 2007 at 3:04 am
OK.
The table holds records of updates to other tables - kind of an 'update history' - BUT all the updateno values are at 0 at present. They need to be incremented, 0 to (whatever) for their 'item' (the reference to the other data), and the unique value per entry, for each item, is the updatedate.
Most items have only the one entry, but some have up to several.
So, I need a way to simply scan the contents and change the 'updateno' value to 1/2/3/4 - whatever - in sequence for its item.
Helps? or still clear as mud?
January 16, 2007 at 3:17 am
Yes, I think that's how I understood it. Did you try the code I posted yesterday? It should do precisely what you want.
Column "pk" in mytable is the column that identifies 'item', i.e. the reference to other data. Sorry for the confusion, it should have been rather FK (it isn't a primary key, but foreign key to some other table, or something that is very similar to FK). In your case it could even be several columns, but that doesn't change anything.
January 17, 2007 at 2:19 am
not had the chance to, but will today!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply