November 3, 2002 at 4:03 am
Hello all
I have a situation like this.
I have an Items table where I have itemID and ItemDescription.I has data as follows:
1 pepsi
2 coke
3 fanta
4 sprite
....
....
....
1001 NULL
1002 NULL
1003 NULL
1004 NULL
1005 NULL.
Now I want to update the table, the same value for item descriptopn from 1 to 5 in 1001 to 1005.
Is it possible to write a single query to solve this? Or Do we need a database program for this?
Please reply AsAP if you have any clues.
Thanks in advance,
Anbu
November 3, 2002 at 4:21 am
Here is a solution, but you'll have to fiddle a bit with the exact matching criteria. This query updates all values larger than 1000 with their counterparts smaller than 1000 (1001 with 1, ... 1010 with 10).
UPDATE Items
SET ItemDescription =
(select ItemDescription
from Items i2
where items.ItemId=i2.itemid+1000
)
where itemid > 1000
November 3, 2002 at 4:59 am
Hi,
Thanks for immediate reply.
But how does it work when I have the item set with different item numbers (not in order). Like starting from 17 to 22.
Thanks in advance,
Anbu
November 3, 2002 at 8:26 am
I'm not sure I get your question?
To use this kind of technique, there has to be a clear one to one relationship between the record you want to update and the record that holds the new value.
In the example, that relationship is the '+1000' bit. The record to update has itemid 1001 and will get its value from record with itemid 1.
As long as you can express that relationship in SQL, you can adjust the query from my previous post.
Maybe, if you give a simple example of what you're trying to achieve, I can give you some more directions.
November 3, 2002 at 9:22 am
No, there is no one-to-one relationship between the two set of item numbers.
November 3, 2002 at 10:03 am
Ok, now it gets difficult...
You could built a table holding all of the relationships. It would just contain the ItemId of the source and of the destination row.
Eg. (could be improved using foreign keys)
CREATE TABLE translate_item
(SrcItemId int,
DstItemId int
)
Next you could do the following query, joining the two tables :
UPDATE Items
SET ItemDescription =
( SELECT ItemDescription
FROM Items i2
INNER JOIN translate_item T
ON i2.ItemId = T.SrcItemId
WHERE items.ItemId=T.DstItemId
)
WHERE itemdescription is null
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply