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

Question

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

Chao Yang 41

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?

Accepted answer

1 additional answer

Your answer

Answer 1

EchoLiu-MSFT 14,621

Hi @Chao Yang ，

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.

Chao Yang 41 Reputation points

2023-02-16T02:16:21.5466667+00:00

Thanks, I know sqlserver ce don't support update-select

Answer 2

Viorel 122.5K

Try some different statements, for example:

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

Chao Yang 41

 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'

Willsonyuan-msft 111 Reputation points

2021-09-09T06:47:22.05+00:00

@Chao Yang 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.
Chao Yang 41 Reputation points

2021-09-09T06:53:53.177+00:00

Oh, it's too hard. 'You cannot declare variables in SQL Compact's SQL dialect.'
Viorel 122.5K Reputation points

2021-09-09T07:37:41.3+00:00

If your real query updates a single row, then you can read the value from table2 using a SELECT, then use a parameterised UPDATE to write it to table1.

Share via

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

1 additional answer

Your answer