我有这个SQL:
UPDATE JOBMAKE SET WIP_STATUS='10sched1'
WHERE JBT_TYPE IN (SELECT JBT_TYPE FROM JOBVISIT WHERE JVST_ID = 21)
AND JOB_NUMBER IN (SELECT JOB_NUMBER FROM JOBVISIT WHERE JVST_ID = 21)
当我把它转换为参数化查询时,它就不起作用了:
UPDATE JOBMAKE SET WIP_STATUS='10sched1'
WHERE JBT_TYPE IN (SELECT JBT_TYPE FROM JOBVISIT WHERE JVST_ID = @jvst_id)
AND JOB_NUMBER IN (SELECT JOB_NUMBER FROM JOBVISIT WHERE JVST_ID = @jvst_id)
Duplicated parameter names are not allowed. [ Parameter name = @jvst_id ]
我尝试过这个方法(我认为它在SQL SERVER 2005中可行 - 不过我没试过):
UPDATE JOBMAKE
SET WIP_STATUS='10sched1'
FROM JOBMAKE JM,JOBVISIT JV
WHERE JM.JOB_NUMBER = JV.JOB_NUMBER
AND JM.JBT_TYPE = JV.JBT_TYPE
AND JV.JVST_ID = 21
There was an error parsing the query. [ Token line number = 3,Token line offset = 1,Token in error = FROM ]
那么,我可以编写动态SQL而不是使用参数,或者传入两个具有相同值的参数,但是否有更好的方法?
Colin