Complex update query

[MikeGarvin]

Hi

I have the code below but have problems trying to convert it into one clean update query. Any help would be greatly appreciated. Thanks.

    Set MyTable = New ADODB.Recordset
    strSQL = "SELECT " & colHdg & ".ID FROM " & _
        "(" & colHdg & " INNER JOIN VoicePPC ON " & colHdg & ".Customer_Index = " & _
        "VoicePPC.CustID) INNER JOIN CustomerVCLI ON (VoicePPC.CLI = CustomerVCLI.CLI) AND " & _
        "(VoicePPC.CustID = CustomerVCLI.CustID) AND (" & colHdg & ".Dialling_Code_Calling = " & _
        ConcatenateFields("CustomerVCLI.NationalDiallingCode", "CustomerVCLI.RegionalDiallingCode") & _
        ") AND (" & colHdg & ".Number_Calling = CustomerVCLI.CLIDetails) WHERE " & _
        "((VoicePPC.CustID = " & custIdx & ") AND (" & colHdg & ".Date_Time_Called >= #" & FormatDateForDB(fromDate, False) & _
        "#) AND (" & colHdg & ".Date_Time_Called <= #" & FormatDateForDB(toDate, False) & "#) AND (" & colHdg & ".Customer_Index = " & custIdx & ") AND " & _
        "(VoicePPC.VoicePricePlan = '" & pPlan & "'))"
    MyTable.Open ModifySQLStatement1(1, strSQL), myMainDbCon, adOpenDynamic, adLockBatchOptimistic, adCmdText
    'debug.print MyTable.RecordCount
    Do Until MyTable.EOF
        DoEvents
        strSQL = "UPDATE " & colHdg & " SET Valid_CLI_For_Plan = 1 WHERE ID = " & MyTable![id]
        myMainDbCon.Execute ModifySQLStatement1(1, strSQL), , adCmdText
        If MyTable.EOF = False Then
            MyTable.MoveNext
        End If
    Loop
    MyTable.Close
    Set MyTable = Nothing

[dee-u]

I cannot test it but one method could be like the following:

strSQL = "SELECT " & colHdg & ".ID FROM " & _
        "(" & colHdg & " INNER JOIN VoicePPC ON " & colHdg & ".Customer_Index = " & _
        "VoicePPC.CustID) INNER JOIN CustomerVCLI ON (VoicePPC.CLI = CustomerVCLI.CLI) AND " & _
        "(VoicePPC.CustID = CustomerVCLI.CustID) AND (" & colHdg & ".Dialling_Code_Calling = " & _
        ConcatenateFields("CustomerVCLI.NationalDiallingCode", "CustomerVCLI.RegionalDiallingCode") & _
        ") AND (" & colHdg & ".Number_Calling = CustomerVCLI.CLIDetails) WHERE " & _
        "((VoicePPC.CustID = " & custIdx & ") AND (" & colHdg & ".Date_Time_Called >= #" & FormatDateForDB(fromDate, False) & _
        "#) AND (" & colHdg & ".Date_Time_Called <= #" & FormatDateForDB(toDate, False) & "#) AND (" & colHdg & ".Customer_Index = " & custIdx & ") AND " & _
        "(VoicePPC.VoicePricePlan = '" & pPlan & "'))"
strSQL = "UPDATE " & colHdg & " SET Valid_CLI_For_Plan = 1 WHERE ID IN (" & strSQL & ")"

[MikeGarvin]

I don't see how what you wrote is different to what I posted. I am looking for a single update query to replace the select and update I posted. Thanks.

[dee-u]

Didn't you notice the following modification I made?

strSQL = "UPDATE " & colHdg & " SET Valid_CLI_For_Plan = 1 WHERE ID IN (" & strSQL & ")"

You can try executing it and see if it works the same, I suppose they should.

[MikeGarvin]

Sorry, will try this.

[MikeGarvin]

I get an error: Operation must use an updateable query. Any ideas?

[dee-u]

How did you use the sql?

[MikeGarvin]

And sorry again. It worked very well. I had missed a bit of your sql out. Thanks for your help with this.

[dee-u]

No problem. Since your problem is solved you can now mark your thread as Resolved using the Thread Tools menu.

[szlamany]

Although UPDATE'ing with a WHERE clause that uses a sub-query is fine - I do it often.

If this is MS SQL - you should know that there is a syntax trick that allows a more natural way of doing this.

If you can say:

Select SomeFld
   From SomeTbl
   Left Join yadda yadda
   Left Join more and more
   Where x.aa = ...

Then you can also say

[code]

Update SomeTbl Set SomeColumn=DDD
   From SomeTbl
   Left Join yadda yadda
   Left Join more and more
   Where x.aa = ...

Notice that the UPDATE has a FROM clause that exactly matches the SELECT's FROM clause.

The real power of this syntax though is that the DDD value that you are using to UPDATE into SomeTbl can come from one of the JOIN's as long as you specify the ALIAS properly.

[MikeGarvin]

Hi

Saw your query does not work under mysql. I get the following error:

You can't specify target table 'story_category' for update in FROM clause.

story_category is the table name in colHdg.

Any idea? Thanks.

[szlamany]

Sorry - I don't use MySQL.

The syntax I showed was for MS SQL.

[MikeGarvin]

Thanks. I tried on Access and MS SQL and it works fine. MySQL does not though. Thanks anyway.