Combining Multiple update queries into one SQL Statement

[Doozer1979]

Hello,

I have multiple update queries that i would like to combine into one statement for performance reasons. I know you can update multiple columns in an update query, but the problem is all the statements have different WHERE clauses. I'm assuming this would be solved by using subqueries, but i'm not sure of the syntax. Would this actaully have performance benefits combining all the queries into one?


UPDATE Recorded_Crime_Committed_Formatted SET Previous7 = True
WHERE Cri_Committed_Date Between Forms!frmMain!prev7Start And Forms!frmMain!prev7End+1;

UPDATE Recorded_Crime_Committed_Formatted SET Current7 = True
WHERE Cri_Committed_Date BETWEEN Forms!frmMain!cur7Start And Forms!frmMain!cur7End+1;

UPDATE Recorded_Crime_Committed_Formatted SET Current14 = True
WHERE Cri_Committed_Date BETWEEN Forms!frmMain!cur14Start And Forms!frmMain!cur14End+1;

UPDATE Recorded_Crime_Committed_Formatted SET Previous14 = True
WHERE Cri_Committed_Date Between Forms!frmMain!prev14Start And Forms!frmMain!prev14End+1;

UPDATE Recorded_Crime_Committed_Formatted SET Current28 = True
WHERE Cri_Committed_Date BETWEEN Forms!frmMain!cur28Start And Forms!frmMain!cur28End+1;

UPDATE Recorded_Crime_Committed_Formatted SET Previous28 = True
WHERE Cri_Committed_Date Between Forms!frmMain!prev28Start And Forms!frmMain!prev28End+1;

UPDATE Recorded_Crime_Committed_Formatted SET Current56 = True
WHERE Cri_Committed_Date BETWEEN Forms!frmMain!cur56Start And Forms!frmMain!cur56End+1;

UPDATE Recorded_Crime_Committed_Formatted SET Previous56 = True
WHERE Cri_Committed_Date Between Forms!frmMain!prev56Start And Forms!frmMain!prev56End+1;

 

[namliam]

how about you do something with IIF to determain which column should be true??

Generaly tho these kinds of fields are "bogus", which you are finding out now. Why not simply do a select on " CN_COMMITED_DATE between..." instead of "Previous56 = true". This prevents your entire update query things....

 

[Doozer1979]

I'm not sure i understand.

The data is used as a record source for excel pivot tables. The reaon we need to set the value to true, is so that users of the pivot tables can easily filter out records that were not in the last 7, 14, 28, or 56 days.

 

[namliam]

Something like so....

UPDATE Recorded_Crime_Committed_Formatted 
SET Previous7 = IIF(Cri_Committed_Date Between Forms!frmMain!prev7Start And Forms!frmMain!prev7End+1;True;False)
,   Current7  = IIF(Cri_Committed_Date BETWEEN Forms!frmMain!cur7Start And Forms!frmMain!cur7End+1; true; FALSE);

tho like I said... Why update stuff, if you can do something simular just in a query? Even if the data is used in excel.

 

[Doozer1979]

Short answer - Because i'm near useless with SQL!

it comes up with a syntax error in the first IIF statement.

Thank you for all your help thus far.

 

[Doozer1979]

Sorted the syntax error out.

 

[namliam]

so issue resolved? And what is your final sql?

 

[Doozer1979]

Worked like a chamr. Thanks man.


Final Sql


UPDATE Recorded_Crime_Committed_Formatted SET Previous7 = IIf(Cri_Committed_Date Between Forms!frmMain!prev7Start And Forms!frmMain!prev7End+1,True,False),
Current7 = IIF(Cri_Committed_Date BETWEEN Forms!frmMain!cur7Start And Forms!frmMain!cur7End+1,true,false),
Current14 = IIF(Cri_Committed_Date BETWEEN Forms!frmMain!cur14Start And Forms!frmMain!cur14End+1,true,false),
Previous14 = IIF(Cri_Committed_Date BETWEEN Forms!frmMain!prev14Start And Forms!frmMain!prev14End+1,true,false),
Previous28 = IIF(Cri_Committed_Date BETWEEN Forms!frmMain!prev28Start And Forms!frmMain!prev28End+1,true,false),
Current28 = IIF(Cri_Committed_Date BETWEEN Forms!frmMain!cur28Start And Forms!frmMain!cur28End+1,true,false),
Previous56 = IIF(Cri_Committed_Date BETWEEN Forms!frmMain!prev56Start And Forms!frmMain!prev56End+1,true,false),
Current56 = IIF(Cri_Committed_Date BETWEEN Forms!frmMain!cur56Start And Forms!frmMain!cur56End+1,true,false);