• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

Updat query error due to lock violations

i have a query that should update TABLE District 3 total list1 with data from
QUERY Calculate next call date.

The query will run, then give me an error that none of the updates were made due to lock violations. Can anyone help me with this?

Thanks!! Beth
UPDATE [District 3 total list1] INNER JOIN [Calculate next call date] ON [District 3 total list1].[Facility ID] = [Calculate next call date].[Facility ID] SET [District 3 total list1].NEXTCALL = [Calculate next call date].[Next Call Date], [District 3 total list1].[CC Band] = [Calculate next call date].[New CC Band];

Open in new window

0
ba_trainer
Asked:
ba_trainer
  • 5
  • 4
1 Solution
 
ba_trainerAuthor Commented:
The Calculate next call date uses fields from District 3 total list1 for its calculations. Could this be the problem? How would I avoid the table locking?
0
 
Rey Obrero (Capricorn1)Commented:
check if you have the same Data Type for the fields updated

--------------------vvvvvvv------------------------------vvvvvvvvvv
District 3 total list1].NEXTCALL = [Calculate next call date].[Next Call Date],
[District 3 total list1].[CC Band] = [Calculate next call date].[New CC Band]
0
 
ba_trainerAuthor Commented:
I did check data types first, they seem fine.

The error is" Microsoft Office didn't update 0 file(s) due to a type conversion failure, 0 record(s) due to key violations, 4517 record(s) due to lock volations, and 0 record(s) due to validation rule violations".

Does that help?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rey Obrero (Capricorn1)Commented:
is [Calculate next call date] a query?
post the SQL here
0
 
ba_trainerAuthor Commented:
Calculate next call date query:
SELECT [District 3 total list1].[Facility ID], [LASTCALLED]+(367/[CC Band]) AS [New Call Date]
FROM [District 3 total list1];

Update last call in the current call cycle query:
UPDATE [District 3 total list1] INNER JOIN [Calculate next call date] ON [District 3 total list1].[Facility ID] = [Calculate next call date].[Facility ID] SET [District 3 total list1].NEXTCALL = [Calculate next call date].[New Call Date];
0
 
Rey Obrero (Capricorn1)Commented:
normally, you don't store calculated values to the table, where you can always extract the values using a query.

0
 
ba_trainerAuthor Commented:
I need to update the table with values that are calculated in the query. I don't know any other way to do it. Can you suggest something?

I need to get the values FROM the table, do the calculations, then UPDATE the table with the new calculations.?

Thanks!!!
0
 
Rey Obrero (Capricorn1)Commented:
try this update query

UPDATE [District 3 total list1]
SET NEXTCALL = [LASTCALLED]+(367/[CC Band])
0
 
ba_trainerAuthor Commented:
Wonderful!! Thanks a million!!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now