May 18, 2011 at 10:24 am
we have a table structure similar to the following:
Key - Pos - Data1 - Data2 - Email
1 - 1 - Room2 - Null - jdoe@hotmail.com
1 - 2 - Null - Null - jdoe@gmail.com
2 - 1 - Null - Null - janedoe@hotmail.com
the Key field and Pos combine to make the primary key. Pos is used to determine the latest or current entry and the additional Pos records serve as a history and/or storing multivalue information.
What i need to be able to do is insert a new record and move just that columns information to the next position. So if i were to insert a new email address for Key 1 then it would look like this:
Key - Pos - Data1 - Data2 - Email
1 - 1 - Room2 - Null - jdoe@yahoo.com
1 - 2 - Null - Null - jdoe@hotmail.com
1 - 3 - Null - Null - jdoe@gmail.com
2 - 1 - Null - Null - janedoe@hotmail.com
then if i needed to add another data1 field to key 1 it would look like this:
Key - Pos - Data1 - Data2 - Email
1 - 1 - Room99 - Null - jdoe@yahoo.com
1 - 2 - Room2 - Null - jdoe@hotmail.com
1 - 3 - Null - Null - jdoe@gmail.com
2 - 1 - Null - Null - janedoe@hotmail.com
Not sure how to do this in the best and fastest way. I'm not really a big fan of this database design but its what i have to work with. Thanks for any input anyone can provide.
May 18, 2011 at 12:49 pm
Ryan Tiebout (5/18/2011)
we have a table structure similar to the following:
Key - Pos - Data1 - Data2 - Email
1 - 1 - Room2 - Null - jdoe@hotmail.com
1 - 2 - Null - Null - jdoe@gmail.com
2 - 1 - Null - Null - janedoe@hotmail.com
the Key field and Pos combine to make the primary key. Pos is used to determine the latest or current entry and the additional Pos records serve as a history and/or storing multivalue information.
What i need to be able to do is insert a new record and move just that columns information to the next position. So if i were to insert a new email address for Key 1 then it would look like this:
Key - Pos - Data1 - Data2 - Email
1 - 1 - Room2 - Null - jdoe@yahoo.com
1 - 2 - Null - Null - jdoe@hotmail.com
1 - 3 - Null - Null - jdoe@gmail.com
2 - 1 - Null - Null - janedoe@hotmail.com
then if i needed to add another data1 field to key 1 it would look like this:
Key - Pos - Data1 - Data2 - Email
1 - 1 - Room99 - Null - jdoe@yahoo.com
1 - 2 - Room2 - Null - jdoe@hotmail.com
1 - 3 - Null - Null - jdoe@gmail.com
2 - 1 - Null - Null - janedoe@hotmail.com
Not sure how to do this in the best and fastest way. I'm not really a big fan of this database design but its what i have to work with. Thanks for any input anyone can provide.
This reads like Data1 is not actually linked to Email even though they are on the same row. Is that correct? Is it that the emails are all alternate emails for the Key?
I can see why you're not a fan of this "design".
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 18, 2011 at 1:03 pm
Hi,
Yes you are correct. the only keys are the key and pos. The data1/data2/email columns aren't linked together. They are used as multivalues for the key column and kept in order by the pos column.
any ideas on how to perform this insert/update?
Thanks again.
May 18, 2011 at 3:41 pm
The best way to deal with this long to term is to fix the database design. What would I have to do in order to make the design change?
May 18, 2011 at 4:46 pm
For a single row insert, this is how I would approach it:
First, start a transaction...
Update the rows associated with the key - changing the position:
UPDATE table
SET pos = pos + 1
WHERE key = @key;
The above makes room for the new inserted row. Create the new row for position 1 with the current or updated values.
INSERT INTO table (Key, Pos, Email)
VALUES (@key, 1, @email);
At this point, you now have row 1 updated with the new value for the column being updated. Row2 should have the values that existed prior, and so on - with Null values assigned for both Data1 and Data2.
The final step is to update the other columns. This should bubble up each column to get us the current value in the first position, and null out the last rows data for the columns not being updated.
UPDATE t
SET t.Data1 = t1.Data1
,t.Data2 = t2.Data2
FROM table t
LEFT JOIN table t1 ON t1.pos + 1 = t.pos -- for pos 1, get pos 2 values
WHERE Key = @key;
Commit the transaction. Put all of this in a TRY/CATCH block and put a rollback into the catch as well as trapping any errors.
Repeat this code for each column being updated - changing the insert to work with the column you are updating and the update to work on the columns not being updated.
This is untested, and I have no way of knowing if it will in fact work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 18, 2011 at 5:54 pm
Thanks Jeffrey. I like that idea. i'll have to see if i can adapt it to work with our database.
I was trying to come up with some kind of stored procedure that would do similar to what you have written.
Thanks again.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply