CODEWITHSUNDEEP
sqlms-access
user3204048
user3204048

Reputation: 11

Combining multiple update queries

I am trying to update a master table from multiple auxilliary tables in Access. I was hoping to use multiple SQL statements within the confines of one query to do this. See the multiple statements below. Is there a way to write this to update both fields in the master table and use language to connect both statements within the same query? I am hoping that I don't have to create 60 update queries to do this.

UPDATE [Master Output Table], [Input table] 
SET [Master Output Table].[Field 1] = [Input table]![Field 1]
WHERE ((([Input table]![Field 1])="AAAA") AND (([Master Output Table]![Field 1])="BBBB"));

UPDATE [Master Output Table], [Input table] 
SET [Master Output Table].[Field 1] = [Input table]![Field 1]
WHERE ((([Input table]![Field 1])="CCCC") AND (([Master Output Table]![Field 1])="DDDD"));

Upvotes: 1

Views: 1379

Answers (2)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

You can also create stored procedure and just pass 2 inputs. Then you need to call stored procedure 60 times. You can stored procedure as follows

Create Procedure UpdateTable
(
      @field1 VARCHAR(200),
      @field2 VARCHAR(200)

)
As
Begin
   UPDATE [Master Output Table], [Input table] 
   SET [Master Output Table].[Field 1] = [Input table]![Field 1]
   WHERE ((([Input table]![Field 1])= @field1 ) AND (([Master Output Table]![Field 1])= @field2));

End

Then call it like Execute UpdateTable 'AAA','BBB'

Upvotes: 1

D Stanley
D Stanley

Reputation: 152556

Since everything else seems to be the same you should be able to just OR your here clauses:

UPDATE [Master Output Table], [Input table] 
SET [Master Output Table].[Field 1] = [Input table]![Field 1]
WHERE ((([Input table]![Field 1])="AAAA") AND (([Master Output Table]![Field 1])="BBBB"))
  OR
      ((([Input table]![Field 1])="CCCC") AND (([Master Output Table]![Field 1])="DDDD"))

Upvotes: 0

Related Questions