Hi all, Implementing a custom Incident Management system for a customer these days I started messing around with a few database tables, and was wondering if somebody could help me here: trying to do an update statement on joined tables in Postgres.
Table Protocols [pr_PK, pr_Attendant, pr_RespDepartment, pr_Description, ...] Table Departments [de_PK, de_Name] Table Employees [em_PK, em_Name, ...] (employee can work in more than one department or change department over time) Table Responsables [re_PK, re_pr_PK, re_em_PK, re_de_PK, re_Timestamp, re_Responsable] When the responsability for an incident (protocol) with number X is taken over by another employee I do the following: BEGIN; UPDATE Responsables SET re_Responsable=false WHERE re_pr_PK=X; INSERT INTO Responsables (re_pr_PK, re_emPK, re_de_PK, re_Timestamp, re_Responsable) VALUES (X, new_em_PK, new_de_PK, NOW(), 'true'); UPDATE Protocols SET pr_RespDepartment=new_de_PK WHERE pr_PK=X; COMMIT; Now there is some old mess in the protocol table from when tracking was not done that extensive and I want to update these as well. Some Googling led to: UPDATE Protocols SET pr_RespDepartment=re_de_PK FROM (Protocols as Prots LEFT JOIN Responsables on re_pr_PK=Prots.pr_PK) WHERE re_Responsible=true; However, it updates in the WHOLE table Protocols ALL rows with the value for re_de_PK it found in the first row of the join, and this is not what I want. (good I am working on a local system with a copy of part of their dataset... but will need the SQL at the moment of installing the new stuff). I have some duplicity in the tables of the JOIN and basically I want all data from the field in the one table to be updated to be equal to the value of the corresponding field in the other table, for all records. Anybody here know how to create the corresponding SQL? An UPDATE using a JOIN? (I have wondered about this in MySQL as well, as it does happen ones in a while, especially when changes in structure of a database are implemented and one wants to update batchwise large amount of data once) Marc ------------------------ Yahoo! Groups Sponsor --------------------~--> AIDS in India: A "lurking bomb." Click and help stop AIDS now. http://us.click.yahoo.com/VpTY2A/lzNLAA/yQLSAA/HKFolB/TM --------------------------------------------------------------------~-> Community email addresses: Post message: [email protected] Subscribe: [EMAIL PROTECTED] Unsubscribe: [EMAIL PROTECTED] List owner: [EMAIL PROTECTED] Shortcut URL to this page: http://groups.yahoo.com/group/php-list Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php-list/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
