May 9, 2010 at 5:11 pm
I have two tables, A and B.
I am updating Table B with info from Table A, but at the same time I would like to update Table A with info from Table B.
So as a quick example:
Table A
ID Name Complete
1 Sam Null
2 Bill Null
3 Greg Null
Table B
ID Name Complete
1 Null Yes
2 Null No
3 Null Yes
So in this case I would match to match on ID, and update T with the complete column, and B with the Name column.
May 9, 2010 at 6:04 pm
You can't update two tables in one SQL statement but you can wrap them in an transaction so from the outside it looks like they are.
BEGIN TRANSACTION
UPDATE A
SET Complete = (SELECT Complete FROM B WHERE B.ID = A.ID)
UPDATE B
SET Name = (SELECT Name FROM A WHERE A.ID = B.ID)
COMMIT
May 10, 2010 at 7:56 am
You may also be able to use an AFTER trigger on table B to update the relevant data in table A. Still two UPDATE statements internally, but it appears to the programmer to be one
You may be able to define a VIEW over both tables, and use an INSTEAD OF trigger on the view to UPDATE both tables. Still two UPDATE statements, but it appears to the programmer to be one
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply