StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your second attempt doesn't work because, based on the <a href="http://msdn.microsoft.com/en-gb/library/ms174122.aspx" rel="nofollow noreferrer">Books On-Line entry for UPDATE</a>, SQL CE does't allow a <code>FROM</code> clause in an update statement.</p> <p>I don't have SQL Compact Edition to test it on, but this might work:</p> <pre><code>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 ) </code></pre> <p>It may be that you can alias JOBMAKE as JM to make the query slightly shorter.</p> <p><strong>EDIT</strong></p> <p>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:</p> <pre><code>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 ) </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload