Joined Tables

[DonP]

In SQL Server 2000, I have an auto numbered ID field that is joined to another non-auto numbered primary key field in a second table. When inserting a record into the first table, I want the second table to also have the same value in one of its fields yet when I save, the second table remains unchanged. Can this not be done with a simple join and if not, what is the way to do it? I am using UltraDev for the developement and don't fully understand its SQL statements so I hesitate to do an inner join that way, manually. I am hoping that SQL Server itself can take care of it. Thanks in advance for any help that might be offered. Don
don@pc-homepage.com

http://www.pc-homepage.com/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[rac2]

Hello Don,

I seem to recall that UPDATE can only affect one table. Even though it can be used with a VIEW, only one table can be updated. It cannot be used with a JOIN. These limitations might not be true in SQL 2000.

If you can create a stored procedure and use it for the process of adding new data to your tables, then -

CREATE PROCEDURE addPlanets (
   @name VARCHAR(20),
   @orbit_radius FLOAT
)

AS

INSERT INTO planets (name, orbit_radius)
VALUES ('Earth', 96E6)

INSERT INTO newMarkets (planet_id)
VALUES (@@IDENTITY)

The trick here is using the system function @@IDENTITY to capture the auto-numbered ID value. My example assumes that the planets table has the auto-numbered key value in a column named planet_id and that the newMarkets table uses this as a foreign key.

Richard

 

[DonP]

Thanks Richard.

I think the problem is that I misunderstood what Join does when used at the SQL Server level, not in the scripts. What I think I'll have to do is to not auto-number the ID field in the first table but instead let the script obtain the highest existing number (plus 1) and insert it into both tables. Ultimately there will be a third table too so maybe this is the best way, though it still seems like a work-around to me.

UltraDev itself is very powerful but makes it difficult to edit the SQL statement manually because it generates it on the fly. Don
don@pc-homepage.com

http://www.pc-homepage.com/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)