Geek asks and answers

How to upgrade a Sql Server CE CE table from another table

I have this 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)

It works until I turn it into a parameterized query:

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 ]

I tried this (which I think will work in SQL SERVER 2005 - although I have not tried it):

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 ]

So, I can write dynamic sql instead of using parameters, or I can pass 2 parameters with the same value, but does anyone know how to do this better?

Colin

+4
source share
1 answer

Your second attempt does not work, because Books- based On-Line for UPDATE SQL CE does not allow FROMthe update statement.

I don't have SQL Compact Edition to test it, but this might work:

UPDATE JOBMAKE
SET WIP_STATUS = '10sched1'
WHERE EXISTS (SELECT 1
              FROM JOBVISIT AS JV
              WHERE JV.JBT_TYPE   = JOBMAKE.JBT_TYPE
              AND   JV.JOB_NUMBER = JOBMAKE.JOB_NUMBER
              AND   JV.JVST_ID    = @jvst_id
             )

, JOBMAKE JM, .

100% SQL CE, , ( JOBMAKE, JOBVISIT). EXISTS SQL, , , . , , SQL CE :

UPDATE JOBMAKE 
SET WIP_STATUS = (SELECT JV.RES_CODE 
                  FROM JOBVISIT AS JV 
                  WHERE JV.JBT_TYPE = JOBMAKE.JBT_TYPE 
                  AND   JV.JOB_NUMBER = JOBMAKE.JOB_NUMBER 
                  AND   JV.JVST_ID = 20
                 )

. , JOBMAKE JOBVISIT. ( ), .

+5

More articles:


All Articles