what you just said is what my procedure does. No matter what you do it never updates the actual part number, but does inserts and deletes even though the user might see it as simply changing the part number.

simply doing inserts and deletes will not work:

Say you start with Part A and Part B:

the user swaps Part A to Part B and vise versa

Just doing inserts and deletes if you updated the part A record first you would delete the part A record and then reinsert it as Part B - Boom you just duplicated Part B before you had a chance to delete the Part B and reinsert it as Part A and the database throws an exception.

What my procedure does is look for these key swaps and where they exist, instead of of deleting or inserting records it updates the records with the fields of the key that it was swapped with.

for those records that have had changes to their partnumber and there isn't another record with that has it's old part number- then I simply delete and insert

here's the code:

if it helps:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExelInventory.Source.Classes.General;
using ExelInventory.Source.Classes.General.DbItems;
using ExelInventory.Source.Classes.General.KeyManagement;


/* Will match old keys to new keys as I means of not updating keys but deleting/inserting them instead */
namespace ExelInventory.Source.Testing
{
class test<TKey, TDbItem>
where TDbItem : AbstractDbItem, IEditableKey<TKey>, IHasKey<TKey>
where TKey : IComparable
{

protected static int GroupNumber = 0;
protected static List<db<TKey, TDbItem>> pairs { get; set; }

public static void getItems(IHasDbItems<TDbItem> Items)
{

//get a list of items that have key changes (compare to result not equal to zero)
IEnumerable<TDbItem> jack = from item in Items.GetUpdatedItems()
where item.OldKey.CompareTo(item.NewKey) != 0
select item;


//----------------------------- With the Items with key changes ------------------------------------

//queries list for matching keys
//IEnumerable<db<TKey, TDbItem>> query = from newKeys in jack
// join oldKeys in jack on newKeys.NewKey equals oldKeys.OldKey
// select new db<TKey, TDbItem>(oldKeys, newKeys);

//queries for matching keys and new keys without matches
IEnumerable<db<TKey, TDbItem>> query = from newKeys in jack
from oldKeys in jack.Where(x => x.OldKey.CompareTo(newKeys.NewKey) == 0).DefaultIfEmpty()
select new db<TKey, TDbItem>(oldKeys, newKeys);

//now query for old key values without a matching new and add them to the list
IEnumerable<db<TKey, TDbItem>> query2 = from values in
(from oldKeys in jack.Where(x => x.OldKey.CompareTo((long)0) != 0)
from newKeys in jack.Where(x => x.NewKey.CompareTo(oldKeys.OldKey) == 0).DefaultIfEmpty() //do not match new items
select new { oldKeys, newKeys }).Where(x => x.newKeys == null)
select new db<TKey, TDbItem>(values.oldKeys, values.newKeys);


pairs = new List<db<TKey, TDbItem>>(query.Union(query2));
GroupNumber = 0; //reset group number
foreach (db<TKey, TDbItem> item in pairs)
{
FindMatches(item);
}

}

protected static void FindMatches(db<TKey, TDbItem> item)
{

db<TKey, TDbItem> match = null;
//if the item is not a deleted part
if (item.NewItem != null)
{
var query = from values in pairs.Where(
x => !(x.Equals(item)) && //do not match the same item
x.GroupId == 0 && //item has not already been matched
x.OldItem != null && //do not invoke a property on a null reference!
x.OldItem == item.NewItem) //if the new item matches the old- bingo
select values;
if (query.Count() > 0)
{
match = query.First();
if (item.GroupId == 0)
{
GroupNumber++;
item.GroupId = GroupNumber;
}
match.GroupId = item.GroupId;
FindMatches(match); //recursively find
}

}
else if (item.OldItem != null)
{
var query = from values in pairs.Where(
x => !(x.Equals(item)) && //do not match the same item
x.GroupId == 0 && //item has not already been matched
x.NewItem != null && //do not invoke a property on a null reference!
x.NewItem == item.OldItem) //if the new item matches the old- bingo
select values;

if (query.Count() > 0)
{
match = query.First();
if (item.GroupId == 0)
{
GroupNumber++;
item.GroupId = GroupNumber;
}
match.GroupId = item.GroupId;
FindMatches(match); //recursively find
}
}
}
}
}

namespace ExelInventory.Source.Testing
{
class db<Tkey, TDbItem> where TDbItem:AbstractDbItem, IHasKey<Tkey>
{
public TDbItem OldItem { get; set; } //reference to the item with the old key
private TDbItem m_NewItem; //reference to the item with the new key
private TDbItem m_TempItem = Activator.CreateInstance<TDbItem>(); //used to store values until transaction completes successfully
public int GroupId { get; set; } //used as an id to group related updates together

public db(TDbItem oldItem, TDbItem newItem)
{
this.OldItem = oldItem;
this.NewItem = newItem;
}

public TDbItem NewItem
{
get { return m_NewItem; }
set
{
if (value != null)
{
//use reflection to set all properties of the temp item to that of the new key
m_NewItem = value;
foreach (PropertyInfo info in TempItem.GetType().GetProperties())
{
if (info.CanRead && info.CanWrite)
{
m_TempItem.GetType().GetProperty(info.Name).SetValue(m_TempItem, info.GetValue(NewItem, null), null);
}
// now set the record as unchanged
}
}
}
}

public TDbItem TempItem
{
get { return m_TempItem; }
}

public DatabaseAction DbAction
{
get
{
DatabaseAction result;
if(NewItem == null)
result = DatabaseAction.delete;
else if(OldItem == null)
result = DatabaseAction.insert;
else
result = DatabaseAction.update;
// insert logic here to decide what action to take
return result;
}
}

public MySqlDateTime TimeStamp
{
get{
switch(DbAction)
{
case DatabaseAction.insert:
return NewItem.Timestamp;
default:
return OldItem.Timestamp;
}
}
}
public String User
{
get{
switch(DbAction)
{
case DatabaseAction.insert:
return NewItem.User;
default:
return OldItem.User;
}
}
}
public Tkey Key
{
get
{
switch (DbAction)
{
case DatabaseAction.insert:
return NewItem.Key;
default:
return OldItem.Key;
}
}
}

public void CommitChanges()
{
//use reflection to set all properties of the new key to that of the temp item
foreach (PropertyInfo info in TempItem.GetType().GetProperties())
{
if (info.CanRead && info.CanWrite)
{
NewItem.GetType().GetProperty(info.Name).SetValue(NewItem, info.GetValue(TempItem, null), null);
}
// now set the record as unchanged
}
NewItem.SetRecordAsUnchanged();
}

}

public enum DatabaseAction
{
delete, update, insert
}