multiple table updates?

[txbakers]

Say I have two tables, one with the list of positions, one with the list of members, which indicates a position.

If someone wants to modify a position, right now the update command changes the positions table. How will it also change the members table? Must I write the join command in the DataAdapter statement?

Do I need to bring in the joins in my original dataset?

Is there a better way, perhaps by using a code, or the primary ID as a foreign key in the members table?

I want to avoid having the users make codes. the text link is easier, but a codes system might be better.

I suppose I can read the max value of a code field first, then write max + 1 as the next code on an insert.

[plumsauce]

I think you're on mysql, but in mssql:

begin transaction
update positions set name="waiter" where name="busboy"
update members set name="waiter" where name="busboy"
commit transaction

the reason for the transaction is to maintain referential integrity.

now, if you were to use identities, that is, autoincrementing integer keys, then you would only have to do:

update positions set name="waiter" where id in (select id from positions where name="busboy")

the above is the generic case where you only know the name and not the id.

or, you could do it in a trigger

Plumsauce

[aspdaddy]

>How will it also change the members table

It wont need to, if members and positions are linkled with a foreign key constraint, then you have ref integrity and dont need transaction/triggers.

Members (MemberID,MemberName, PositionID)
Positions(PositionID, PositionName)

or if members can have many positions..
Members (MemberID,MemberName)
Positions(PositionID, PositionName)
MemberPositions(MemberID,PositionID)

[txbakers]

I'm doing it in .NET and using a datagrid, so I don't get to write the update statement, just get to call the update method of the datagrid.

[TheNige]

I've personally never used the update methods of a datagrid...was always easier to just have control of the sql statements and write the updates yourself.

[txbakers]

D'OH! I didn't think that was possible! I guess it is. I can capture the editCommand and do with it what I want, right?

[TheNige]

yup...datagrids are nice but you don't have to depend on them. You can just get your data from the db and bind it to a datalist or repeater, make your edit screen, etc. all using simple sql queries for each when you need them.