如何从另一个表更新 Sql Server CE 表

问题描述：

我有这个 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 parameterised 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 ]

我尝试过这个(我认为它可以在 SQL SERVER 2005 中工作 - 虽然我还没有尝试过):

I tried this (which i think would work in SQL SERVER 2005 - although I haven't 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 ]

所以，我可以编写动态sql而不是使用参数，或者我可以传入2个具有相同值的参数，但是有人知道如何更好地做到这一点吗?

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

科林

答

您的第二次尝试不起作用，因为基于 Books On-Line entry for UPDATE，SQL CE 不允许在更新语句中使用 FROM 子句.

Your second attempt doesn't work because, based on the Books On-Line entry for UPDATE, SQL CE does't allow a FROM clause in an update statement.

我没有 SQL Compact Edition 来测试它，但这可能有效:

I don't have SQL Compact Edition to test it on, 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 以缩短查询时间.

It may be that you can alias JOBMAKE as JM to make the query slightly shorter.

编辑

我不是 100% 确定 SQL CE 的局限性，因为它们与评论中提出的问题有关(如何使用来自 JOBVISIT 的值更新 JOBMAKE 中的值).我遇到的任何 SQL 方言都不支持尝试在外部查询中引用 EXISTS 子句的内容，但您可以尝试另一种方法.这未经测试但可能有效，因为看起来 SQL CE 支持相关子查询:

I'm not 100% sure of the limitations of SQL CE as they relate to the question raised in the comments (how to update a value in JOBMAKE using a value from JOBVISIT). Attempting to refer to the contents of the EXISTS clause in the outer query is unsupported in any SQL dialect I've come across, but there is another method you can try. This is untested but may work, since it looks like SQL CE supports correlated subqueries:

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 中的多于一行，则此查询将失败.如果这不起作用(或者您不能直接将内部查询限制为每个外部行一行)，则可以使用游标执行逐行更新.

There is a limitation, however. This query will fail if more than one row in JOBVISIT is retuned for each row in JOBMAKE. If this doesn't work (or you cannot straightforwardly limit the inner query to a single row per outer row), it would be possible to carry out a row-by-row update using a cursor.

如何从另一个表更新 Sql Server CE 表

相关推荐