Convert MS Access Update Query to SQL Query-CodePudding

I have an MS Access Query:

'''UPDATE MyFloridaNetDataDump INNER JOIN RepoVoipAcsNonRecurring ON 
'''MyFloridaNetDataDump.service_modified = RepoVoipAcsNonRecurring.Validation 
'''SET RepoVoipAcsNonRecurring.Invoice = MyFloridaNetDataDump.Invoice_modified, 
'''RepoVoipAcsNonRecurring.Amount = '''Round(MyFloridaNetDataDump.billable_charge*RepoVoipAcsNonRecurring.Percentage,2), 
'''RepoVoipAcsNonRecurring.Billing Cycle = MyFloridaNetDataDump.bill_cycle;

I need to convert it to SQL, which I'm using in a .net application. I have been able to convert most of the query, but when I try to perform the multiplication I get errors depending on what I leave in. Meaning if I take the ROUND function out it doesn't like the * multiplication. If I remove the multiplication line, the query runs.

'''UPDATE MyFloridaNetDataDump
'''SET MyFloridaNetDataDump.InvoiceModified = RepoVoipAcsNonRecurring.Invoice,
'''MyFloridaNetDataDump.BillableCharge * RepoVoipAcsNonRecurring.Percentage = '''RepoVoipAcsNonRecurring.Amount, 
'''MyFloridaNetDataDump.BillCycle = RepoVoipAcsNonRecurring.BillingCycle
'''FROM RepoVoipAcsNonRecurring
'''WHERE MyFloridaNetDataDump.ServiceModified = RepoVoipAcsNonRecurring.Validation

CodePudding user response：

I think you inverse all of your set columns I would suggest you to do like this

UPDATE r
SET 
    Invoice = m.InvoiceModified, 
    Amount = Round(m.billablecharge*r.Percentage,2), 
    [BillingCycle] = m.billCycle
FROM 
    MyFloridaNetDataDump m 
    INNER JOIN RepoVoipAcsNonRecurring r ON m.serviceModified = r.Validation