Hi everybody, I have to update a 'note' field of a 'register' table with the values from the correspective entries of an 'old_register' table. The 'old_register' table is shorter than 'register', I mean it has less records, but every primary key's value in 'old_register' is also the value of one record of 'register'. In other words, if I just could succeed in the update, the resulting 'register' table's record set would be a superset of the 'old_register' 's one. BTW in both tables the tuple (person_id, document_id) is the primary key.
Here's the query: update register set note = (select ORT.note from old_register ORT where ORT.person_id = person_id and ORT.document_id = document_id) where exists ( select ORT.* from old_register ORT where ORT.person_id = person_id and ORT.document_id = document_id); Well, it just keeps updating the 'note' field of ALL the records of 'register' with the 'note' field's value of the first record of 'old_register'. Weird, isn't it? And most of all, the following query works, I mean it correctly returns the only records of 'register' which have a correspondant record in 'old_register' whith the same values for the (primary) key (person_id, document_id)! select RT.person_id, RT.document_id, RT.note from register RT where exists ( select ORT.* from old_register ORT where ORT.person_id = RT.person_id and ORT.document_id = RT.document_id) One might think that it is a problem of full qualification of the field names, but if you try to fully qualify the person_id and document_id in the 'update' query, sqlite raises an error. It drives me mad, please help! Thanks, C. _________________________________________________________________ Racconta le tue emozioni sul blog! http://home.services.spaces.live.com/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
