How to update ID values in multiple tables

How to update ID values in multiple tables

• Nung

  Are you familiar with foreign key relationships and cascading updates/deletes That's one option (that some would frown on). It depends on how "big" of a system this is. My other question would be is a particular PC (say PC44) always associated with the same set of other items (say printer19 and mouse88) or when a user is "disassociated" do all the items go back into the available pool to be used in any combination the next time
  

• Anurag G

  That's easy using the PC_Equipment table I created in the above schema:

  Table PC_Equipment(PCID, EquipID)

  Here are a few example rows:

  PCID EquipID
  ---- -------
  pc44 mon28
  pc44 pri12
  pc44 key07
  pc44 mse99

  Now say you wanted to move monitor 28 to pc 13. Delete the first row I listed above and add this row:

  pc13 mon28

  Make sense
  Bob

  
  

• JamesAins

  Thanks for the advice. I understand that when a user is disassociated from a PC then a row would be deleted from the User_PC table therefore the items that belong to the PC remain intact.

  My other problem would be when an individual item assigned to the PC such as a monitor, printer etc has to move to another PC as these activities can occur as well. How would I get around this problem.

  Regards

  Dipendra

  
  

• BrianBrian

  In my company a PC would always be associated with the same set of other items and when a user is disassociated, another user has to acquire those items rather than being used in any combination.

  There must be way of getting around this issue.

  
  

• Michael J

  I do understand this example, but I would need to retain the EQUIPID value somehow in order for another PC to be assigned this item. If a DELETE statement is run then this will remove the row permanently or do I need to use an UPDATE statement.

  Regards

  Dipendra

  
  

• jack the ripper

  No, you need to take a little closer look at the schema I created.  There are three tables involved when it comes to the PC's and associated equipment.  There is a PC table which contains the ID and any other info you might need about a PC, the Equipment table that contains the equipment ID and any associated info, and finally the PC_Equipment table which *only* contains a join of PCID's and EquipID's.  Deleting a row from the PC_Equipment table *only* deletes the relationship between the two.  The piece of equipment still exists in the Equipment table and the PC still exists in the PC table.
  

• Rick2K

  So would the following schema (I use that term loosely) work for you:

  Table: User (UserID, Name, etc)

  Table: PC (PCID, Location, etc)

  Table: Equipment(EquipID, Description, etc)

  Table: PC_Equipment(PCID, EquipID)

  Table User_PC(UserID, PCID)

  So the user table has a record of all users keyed on user ID.  The PC table keeps track of all PC's with key PCID.  Likewise the Equipment table keeps track of various pieces of misc equipment (mice, keyboards, etc).  The PC_Equipment table records the one to many relationship between one PC and one or more pieces of equipment.  The User_PC table records the one to one relationship between a given user and a given PC.  If you disassociate a user from a PC, you are deleting a row from User_PC, but the relationship between that PC and its equipment Equipment is unchanged, and it is ready for the next user who comes along. 

   

  HTH,

  Bob

  
  
• How to update ID values in multiple tables