question

ChaoYang-1684 avatar image
0 Votes"
ChaoYang-1684 asked Viorel-1 commented

How to update column from another table column in sqlserver compact 4.0?

I would like to achieve it.

Update table1 set table1.column1 = table2.column2
from table1, table2
where table1.column2 = table2.column2

How to achieve it in sqlserver compact 4.0?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 commented

Try some different statements, for example:

 update table1 
 set table1.column1 = 
     (select table2.column1 from table2 where table2.column2 = table1.column2)


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

  Error Code: 80040E14
  Message   : There was an error parsing the query. [ Token line number = 3,Token line offset = 7,Token in error = select ]
  Minor Err.: 25501
  Source    : SQL Server Compact ADO.NET Data Provider
  Num. Par. : 3
  Num. Par. : 7
  Err. Par. : select

I've tried all the answers on the Internet, but I can't.
sqlserver compact 4.0 doesn't support update 'FROM' / update 'SELECT'

0 Votes 0 ·

@ChaoYang-1684 You are right, sqlserver compact 4.0 doesn't support update 'FROM' / update 'SELECT' based on Books On-Line entry for UPDATE
You'd have to assign a value to a variable in one query, then use the variable to do the update in the second, thus you may try Cursors to achieve it.


0 Votes 0 ·

Oh, it's too hard. 'You cannot declare variables in SQL Compact's SQL dialect.'

0 Votes 0 ·
Show more comments
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ChaoYang-1684

Welcome to the microsoft TSQL Q&A forum!
Sorry, I am not familiar with sqlserver compact, and I have not found a relevant forum to help you.

But I found some solutions, maybe it can help you:
UPDATE on two INNER JOINed tables in SQL Server Compact 4
How to do Sql Server CE table update from another table


If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.