How to do Sql Server CE table update from another table

by

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.

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
             )

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

EDIT

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
                 )

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.

More Related Contents:

  1. How do you open an SDF file (SQL Server Compact Edition)? [closed]
  2. What are the limitations to SQL Server Compact? (Or – how does one choose a database to use on MS platforms?)
  3. “If not exists” fails on SQL CE
  4. How do I use cascade delete with SQL Server?
  5. SQL Server insert if not exists best practice [closed]
  6. An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server
  7. Maximum size of a varchar(max) variable
  8. Why do table names in SQL Server start with “dbo”?
  9. Add alternating row color to SQL Server Reporting services report
  10. LIKE vs CONTAINS on SQL Server
  11. How Do I Split a Delimited String in SQL Server Without Creating a Function?
  12. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  13. How to enable bulk permission in SQL Server
  14. Should every User Table have a Clustered Index?
  15. How to load an XML file into a database using an SSIS package?
  16. TSQL: Create a view that accesses multiple databases
  17. How to connect to MS SQL Server using Inno Setup?
  18. Check if a temporary table exists and delete if it exists before creating a temporary table
  19. SQL count rows in a table
  20. What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication?
  21. How do I programmatically set the connection string for Entity-Framework Code-First?
  22. SQL Error with Order By in Subquery
  23. Executing set of SQL queries using batch file?
  24. How to change default language for SQL Server?
  25. T-SQL incrementing counter based on change in a column value
  26. How can I fill a column with random numbers in SQL? I get the same value in every row
  27. The request failed or the service did not respond in a timely fashion?
  28. Joining tables from different servers
  29. Undesired rounding of DateTime in SQL Server
  30. How to choose the clustered index in SQL Server?

Leave a Comment