UPDATE with SELECT

[anrver]

First my code :

UPDATE adr SET UniCde = (SELECT Unicde FROM adr_cross WHERE adr.AddressID = adr_cross.AddressID)

As long as the primery key of table 'adr_cross' is 'AddressID' everything works fine. When I change the primery key into 'UniCde' i'v got no result.

Is there a way to update my table with 'UniCde' as primery Key ?


Erik Verstichel
Panalpina World Transport.
Antwerp, Belgium

 

[henslecd]

I would suggest taking off the primary key status of UniCde in adr until you get your data into the table. Once that is done then set UniCde to a primary key. If it doesn't let you, it is because you have duplicate values in that column and you must eliminate the duplicates.

When you say no result, do you really mean you are getting an error message? If so, what is it?

Also, it would be helpful to you and everyone who programs behind you to use better naming conventions. If you want a field to be a primary key, use pk in front of the name. fk for foreign key. I know that many examples on the web do not do this, but in the real world it saves a lot of time for programmers who can't figure out why something isn't working, when it could just be a naming mistake.