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

Update multiple fields with inner select

Hello,

I'm wondering if its possible to extend the following query to include multiple set statements based on different where clauses in one query?

Is there an easier way than repeating this query?

UPDATE ProfileData, SummaryTable SET ProfileData.CarValueO1 = [Total]*[CarValue]
WHERE (((SummaryTable.CarType)="Sedan") AND ((SummaryTable.NumberDrivers)="NumberDrivers1") AND ((SummaryTable.NumberClaims)="NumberClaims1"));

Open in new window


Thanks
0
AndyC1000
Asked:
AndyC1000
  • 5
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have described the options to do UPDATE WITH JOIN in this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
the ms access syntax is at the end, in the additional comments, so yes, it's possible
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
of course, you may need to combine that with IIF() expressions, but as your requirements is not 100% clear ...
0
 
AndyC1000Author Commented:
Thanks for your response.

Please provide syntax the to:
SET ProfileData.CarValueO2 = [Total]*[CarValue]
WHERE (((SummaryTable.CarType)="Sedan") And ((SummaryTable.NumberDrivers)="NumberDrivers2") And ((SummaryTable.NumberClaims)="NumberClaims2"));

within the update query below.

UPDATE ProfileData, SummaryTable SET ProfileData.CarValueO1 = [Total]*[CarValue]
WHERE (((SummaryTable.CarType)="Sedan") And ((SummaryTable.NumberDrivers)="NumberDrivers1") And ((SummaryTable.NumberClaims)="NumberClaims1"));

Open in new window

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would go with something along like this (not tested):
UPDATE ProfileData, SummaryTable 
 SET ProfileData.CarValueO1 = IIF( AND( SummaryTable.NumberDrivers = "NumberDrivers1" ; SummaryTable.NumberClaims ="NumberClaims1") ; [Total]*[CarValue] ; CarValueO1 )
  , ProfileData.CarValueO2 = IIF( AND( SummaryTable.NumberDrivers = "NumberDrivers2" ; SummaryTable.NumberClaims ="NumberClaims2") ; [Total]*[CarValue] ; CarValueO2 )
WHERE ((SummaryTable.CarType)="Sedan") ;

Open in new window

0
 
AndyC1000Author Commented:
I ran the query its says: syntax error missing operator in query expression 'IIF( AND ( SummaryTable.NumberDrivers = "NumberDrivers1"; SummaryTable.NumberClaims = "NumberClaims1"); [Total]*[CarValue];

I was unable to save the query because of the syntax error.  I've attached an example database.
profiledb.accdb
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I used ";" you may need to use "," for the arguments...
0
 
AndyC1000Author Commented:
I replaced the semi-colons with commas no change.
0
 
AndyC1000Author Commented:
I made the following change to the query although now it says syntax error in UPDATE statement.

UPDATE ProfileData, SummaryTable 
SET ProfileData.CarValueO1 = IIF( SummaryTable.NumberDrivers = "NumberDrivers1" AND SummaryTable.NumberClaims ="NumberClaims1") , [Total]*[CarValue] , CarValueO1 ), 
ProfileData.CarValueO2 = IIF( SummaryTable.NumberDrivers = "NumberDrivers2" AND SummaryTable.NumberClaims ="NumberClaims2") , [Total]*[CarValue] , CarValueO2 )
WHERE ((SummaryTable.CarType)="Sedan");

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should work better:
UPDATE ProfileData p
INNER JOIN SummaryTable  s
SET p.CarValueO1 = IIF( s.NumberDrivers = "NumberDrivers1" AND s.NumberClaims ="NumberClaims1") , [Total]*[CarValue] , CarValueO1 ), 
p.CarValueO2 = IIF( s.NumberDrivers = "NumberDrivers2" AND s.NumberClaims ="NumberClaims2") , [Total]*[CarValue] , CarValueO2 )
WHERE ((s.CarType)="Sedan");

Open in new window

references:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html           
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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