SQL Compact Bulk Update table from another table

Question

I've hit a little problem. I'm Inserting/Updating over 2k of records and within the 2k, there may be some record that I already have. The once I have already I may need to update the original record etc.

The solution uses SQL Compact and it can run on slow computers. I did have a method, but it took over 10 mins. (Way too long).

So now I do a bulk insert into a temp table, then I can do a insert from the temp to the original.

I seem to have an issue with my update script. The below script works with SQL Server, but when I attempt the same script (with the same database schema etc), it does not work.

Can anyone help?

UPDATE Clients 
SET     Title = Temp_Clients.Title
       ,Forename = Temp_Clients.Forename
       ,Surname = Temp_Clients.Surname
       ,DOB = Temp_Clients.DOB
       ,IsMale = Temp_Clients.IsMale
       ,Address1 = Temp_Clients.Address1
       ,Address2 =Temp_Clients.Address2
       ,Address3 = Temp_Clients.Address3
       ,Town = Temp_Clients.Town
       ,County = Temp_Clients.County
       ,Postcode = Temp_Clients.Postcode
       ,Telephone = Temp_Clients.Telephone
       ,Mobile = Temp_Clients.Mobile
       ,Email =Temp_Clients.Email
     ,LocationID = Temp_Clients.LocationID
    ,GpPractice = Temp_Clients.GpPractice
     ,GpName = Temp_Clients.GpName
FROM Temp_Clients 
INNER JOIN Clients AS A ON A.[CustomClientID] = Temp_Clients.[CustomClientID]

score 0 · Accepted Answer · answered Feb 05 '14 at 14:28

0

I think your from is the wrong way around: It should be client first then joining on the temp_clients.

UPDATE Clients 
SET     Title = Temp_Clients.Title
       ,Forename = Temp_Clients.Forename
       ,Surname = Temp_Clients.Surname
       ,DOB = Temp_Clients.DOB
       ,IsMale = Temp_Clients.IsMale
       ,Address1 = Temp_Clients.Address1
       ,Address2 =Temp_Clients.Address2
       ,Address3 = Temp_Clients.Address3
       ,Town = Temp_Clients.Town
       ,County = Temp_Clients.County
       ,Postcode = Temp_Clients.Postcode
       ,Telephone = Temp_Clients.Telephone
       ,Mobile = Temp_Clients.Mobile
       ,Email =Temp_Clients.Email
     ,LocationID = Temp_Clients.LocationID
    ,GpPractice = Temp_Clients.GpPractice
     ,GpName = Temp_Clients.GpName
FROM Clients INNER JOIN Temp_Clients ON Clients.[CustomClientID] = Temp_Clients.[CustomClientID]

answered Feb 05 '14 at 14:28

Peter_R

642
1
7
12

Thanks for the reply, but sadly its still not working. I get the below error FROM Clients INNER JOIN Temp_Clients ON Clients.[CustomClientID] = Temp_Clients.[CustomClientID] There was an error parsing the query. [ Token line number = 19,Token line offset = 1,Token in error = FROM ] – Chris Cooper Feb 05 '14 at 14:36
Ah sorry with SQL server compact you cannot use that type of query http://msdn.microsoft.com/en-us/library/ms174122(v=sql.105).aspx – Peter_R Feb 05 '14 at 15:01
Thanks. Do you know of any other way I could do what I need with SQL Compact? – Chris Cooper Feb 05 '14 at 16:53
I think your only option is to do a Insert and Delete instead of update. – Peter_R Feb 06 '14 at 09:19

SQL Compact Bulk Update table from another table

1 Answers1