联接上的非唯一表/别名

Question

我打算基于联接进行更新，但得到了一个错误。少了什么？

update
     vna.patients,
     vna.patient_observations,
     vna.studies,
     vna.series,
     vna.instances,
     vna.sop_classes,
     vna.files,
     vna.modalities,
     vna.issuers
join patient_observations on atients.patient_id=patient_observations.patient_id 
join studies
  on  patient_observations.study_id=studies.study_id
  AND studies.patient_id=patients.patient_id
join series
  on series.study_id=studies.study_id
join instances
  on instances.series_id=series.series_id
join sop_classes
  on sop_classes.sop_class_id=instances.sop_class_id
join files
  on files.instance_id=instances.instance_id
left join modalities
  on modalities.modality_id=series.modality_id
left join issuers
  on (patients.issuer_of_patient_identifier=issuers.issuer_id)
set PATIENT_NAME='AAPM'
WHERE PATIENT_IDENTIFIER='TG18-2002';

ERROR 1066 (42000): Not unique table/alias: 'patient_observations'

Answer 1

您不需要再次指定在联接部分中出现的表名

UPDATE 
  vna.patients 
  JOIN patient_observations 
    ON patients.patient_id = patient_observations.patient_id 
  JOIN studies 
    ON patient_observations.study_id = studies.study_id 
    AND studies.patient_id = patients.patient_id 
  JOIN series 
    ON series.study_id = studies.study_id 
  JOIN instances 
    ON instances.series_id = series.series_id 
  JOIN sop_classes 
    ON sop_classes.sop_class_id = instances.sop_class_id 
  JOIN files 
    ON files.instance_id = instances.instance_id 
  LEFT JOIN modalities 
    ON modalities.modality_id = series.modality_id 
  LEFT JOIN issuers 
    ON (patients.issuer_of_patient_identifier = issuers.issuer_id)
  SET PATIENT_NAME = 'AAPM' 
WHERE PATIENT_IDENTIFIER = 'TG18-2002' ;

Answer 2

为什么要混合这两种不同的JOIN语法？简单规则:永远不要在FROM子句中使用逗号(这也适用于UPDATE )。我想你打算：

update vna.patients p
       patient_observations po
       on p.patient_id = po.patient_id join
       studies st
       on po.study_id = st.study_id AND
          st.patient_id = p.patient_id join
       series s
       on s.study_id = st.study_id join
       instances i
       on i.series_id = s.series_id join
       sop_classes sc
       on sc.sop_class_id = i.sop_class_id join
       files f
       on f.instance_id = i.instance_id left join
       modalities m
       on m.modality_id = s.modality_id  left join
       issuers iss
       on (p.issuer_of_patient_identifier = iss.issuer_id)
    set p.PATIENT_NAME = 'AAPM'
    where p.PATIENT_IDENTIFIER = 'TG18-2002';

这似乎太复杂了。我猜你只是想：

update vna.patients p
    set p.PATIENT_NAME = 'AAPM'
    where p.PATIENT_IDENTIFIER = 'TG18-2002';

Answer 3

错误消息非常清楚，查询中的表引用中列出了两次表patient_observations。如果确实需要在同一个查询中再次加入第二个别名，请给它一个不同的别名：

...
vna.issuers 
join patient_observations as po2 on patients. ....

否则，移除其中一个。

还尝试使用语法而不是这种旧的语法。