Merging Column from one Table to another?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merging Column from one Table to another??

Author

Topic

AetherNZ
Starting Member

3 Posts

Posted - 2010-02-07 : 23:33:46

I have been trying to nut this out for quite some time to no avail.

My simplified db looks like:


tblMovies
Title		Date	Rating
The Matrix	2006	Null
Harry Potter	2001	Null
Avatar		2009	Null
LOTR		2005	Null

tblRatings
Title		Rating
Harry Potter	7
LOTR		9

I would like to copy the data from tblRatings.Rating column into the corresponding tblMovies.Rating field, keeping tblMovies intact.

As you can see there is less rows in tblRatings than tblMovies.

I am writing this in C# using SQL-CE.

Any help would be greatly appreciated!

Cheers

Sachin.Nand

2937 Posts

Posted - 2010-02-08 : 01:25:26

UPDATE Tm SET Tm.Rating=Tr.Rating FROM
tblMovies Tm INNER JOIN tblRatings Tr ON Tr.Title=Tm.Title

PBUH

AetherNZ
Starting Member

3 Posts

Posted - 2010-02-08 : 02:10:38

Thanks for your reply. I seem to have an error when executing the code.

"There was an error parsing the query. [ Token line number = 1,Token line offset = 37,Token in error = FROM ]"

Here is my C# code, maybe it's the way I'm doing it?

SqlCeConnection cn = new SqlCeConnection(ConnectString(mode));

if (cn.State == ConnectionState.Closed)
{
     cn.Open();
}

string sql = "UPDATE Tm SET Tm.Rating = Tr.Rating FROM tblMovies Tm INNER JOIN tblRatings Tr ON Tr.MovieTitle = Tm.MovieTitle";

SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.ExecuteNonQuery();

Sachin.Nand

2937 Posts

Posted - 2010-02-08 : 02:44:45

Is it working properly in sql query analyser?

PBUH

AetherNZ
Starting Member

3 Posts

Posted - 2010-02-08 : 03:38:39

"Optional FROM clause not allowed in this query type.
Column or expression 'Rating' cannot be updated"

After a quick search it looks like SQL-CE does't allow a FROM clause in an UPDATE statement.

Do you know of a way around this?

Thanks again.

Sachin.Nand

2937 Posts

Posted - 2010-02-08 : 04:27:18

quote:
Originally posted by AetherNZ

After a quick search it looks like SQL-CE does't allow a FROM clause in an UPDATE statement.

Well I dint know that.
You can use a shile loop through the recordset to update the matching records.I hope that SQL CE atleast supports while loop.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources