insert into query problem

[SuperPower]

Hi,

I'm having some problems. I'm now normalizing the database and I therefore need to write some queries to make my work easier.

I added a field named ClaimantID in my Case table. It's a foreign key, and the primary key is in the Claimants table. Now, I have ClaimNo (not uniqe) as a attribute in the Case table. This value is also in the Claimants table (I will remove it from the Case table as soon as I'm done).

I want to write a query that inserts the ClaimantID from my Claimants table into the Case table where ClaimNo is equal to the ClaimNo in the Claimant table.

To give a illustration:

Case Table:

CaseID.........ClaimantID...........ClaimNo (will remove later)
12343.............blank................UIL872-U
12344.............blank................LIO083-3-5
12345.............blank................IUL45-IU3
12346.............blank................UIL872-U
...etc.

Claimant Table:

ClaimantID............ClaimNo
1..........................UIL872-U
2..........................LIO083-3-5
3..........................IUL45-IU3
...etc.

So, now I want to write a query where I can insert the ClaimantID from the Claimants table into the appropriate recordset in the Cases table. So, CaseID 12343 should have ClaimantID 1, 12344...2, 12345....3, 12346...1, etc.

Hope someone can help me!

Geir

 

[ClaireHCM]

Try this

update cases set cases.claimtid = claimt.claimtid
from cases inner join claimt on cases.claimtno = claimt.claimtno

 

[zhavic]

May also work without join

UPDATE [Case] SET ClaimantID = Claimant.ClaimantID
FROM Claimant
WHERE [Case].ClaimNo = Claimant.ClaimNo


Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[pascalsql]

BTW, allways avoid what looks like a reserved word (here a table named [case] ).

Some features, such as the one creating web pages (sql7) don't automatically generate the brackets, ending up in error hard to debug.

Even the too-often seen column DATE should be avoided. There are so many languages using it in various ways that it will eventually collide.

Set your naming convention to be sure you have some exclusivity on the names you create.