Updating one table to another

[Beast777]

I have a table full of persons and they each have a number which means what type of job they have. These numbers are being replaced. I have a table that lists the first number and the new second number. I created a new field in the pers table and want to have it put in the new number depending on the old. I wrote this query but its not working, help please.

ID = new number
MOC = old number
Persdata is personnel table
ID is the conversion table

update Persdata Set ID =(select Distinct(persdata.ID) from ID where ID.MOC = Persdata.MOC) where exists (select distinct(persdata.ID) from ID where ID.MOC = Persdata.MOC)

 

[JohnDTampaBay]

I take it that ID is the new field, MOC is the old field, and ID is the translation table.

update p
Set ID = i.ID
from persData AS p
  inner join ID AS i
  on p.MOC = i.MOC

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[gmmastros]

I almost hate to post advice on updating data because you can really screw it up.

First, run the select. If it works, then the update

Select *, Persdata.ID As OldNumber, Id.Moc As NewNumber
From   Persdata
       Inner Join ID On Persdata.Id = Id.MOC

Update Persdata 
Set    Persdata.ID = ID.Id
From   Persdata
       Inner Join ID On Persdata.Id = Id.MOC

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DrSql]

I am not wether you need the second select, becasue you are satting a.MOC=b.MOC.
Or try this select stsmnt and verify this wht you are trying to update.

select a.Id, b.MOC from
Persdata a, ID b
where a.id=b.id


update Persdata Set ID =
(select Distinct(persdata.ID) from ID
where ID.MOC = Persdata.MOC)

Dr.Sql
Good Luck.