I am just trying to truncate the first two characters from a varchar field in Subjects.subjectid for the subset of records as specified, but it's not working. I can't spot what's wrong with my code (postgresql):
UPDATE subjects
SET
subjectid = substring(S.subjectid from 2)
FROM
ibg_studies ST,subjects S,dnasample D
WHERE
D.studyindex=ST.studyindex
AND ST.studyabrv='CONGER'
AND D.subjectidkey=S.id
AND D.projectindex IS NULL
Any ideas appreciated.
Your subquery is not coupled (uncorellated) to the table being updated. I don't know what your intentions were, but maybe you want this (just guessing) :
UPDATE subjects sj
SET
subjectid = substring(S.subjectid from 2) -- << what is this?
FROM
ibg_studies st
-- ,subjects s
,dnasample d
WHERE d.studyindex = st.studyindex
AND st.studyabrv = 'CONGER'
AND d.subjectidkey = sj.id -- changed from s to sj?
AND d.projectindex IS NULL
;
The problem seemed to be that SQL had a problem updating a field while calling a string function on the same field. I overcame the limitation by adding a column to the table, updating this new column with the string function, then copying that new column value into the original target field, like this:
-- 6: copy modified subjectid to temp
--UPDATE dnasample D
--SET
-- temp = substring(S.subjectid from 3)
--FROM
-- ibg_studies ST,subjects S
--WHERE
-- D.studyindex=ST.studyindex
-- AND ST.studyabrv='CONGER'
-- AND D.subjectidkey=S.id
-- AND D.projectindex IS NULL
-- 7: copy temp to subjectid
--UPDATE subjects S
--SET
-- subjectid = D.temp
--FROM
-- ibg_studies ST,dnasample D
--WHERE
-- D.studyindex=ST.studyindex
-- AND ST.studyabrv='CONGER'
-- AND D.subjectidkey=S.id
-- AND D.projectindex IS NULL
-- 8: Remove the temp column
ALTER TABLE dnasample DROP COLUMN temp
SELECT S.subjectid FROM ibg_studies ST,subjects S,dnasample D WHERE D.studyindex=ST.studyindex AND ST.studyabrv='CONGER' AND D.subjectidkey=S.id AND D.projectindex IS NULL