Hi,

I am making some alterations to my Database. I have a table called projects
and a table called Work_Types. Projects currently contains the name of the
work type (Work_Type) but now I want to change this so it contains the
Work_Type_ID, is it possible to update Projects with one query?

Thanks for your help

ALTER TABLE Projects ADD work_type_id INTEGER NULL
REFERENCES Work_Types (work_type_id)

UPDATE Projects
SET work_type_id =
(SELECT work_type_id
FROM Work_Types
WHERE work_type = Projects.work_type)

ALTER TABLE Projects DROP COLUMN work_type
ALTER TABLE Projects ALTER COLUMN work_type_id INTEGER NOT NULL

(untested and based on assumed DDL).

--
David Portas
------------
Please reply only to the newsgroup
--