Update a Query Activity validated

Learn how to update a Query Activity in Salesforce Marketing Cloud (SFMC) with SSJS (server-side JavaScript). Code snippets include Core, WSProxy and REST API methods.

Core

var customerKey = "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

var qd = QueryDefinition.Init(customerKey);

var result = qd.Update({
    QueryText: "SELECT TOP 50 SubscriberKey, EmailAddress FROM _Subscribers"
});

<script runat="server">

    Platform.Load("core", "1");

	try {

        var customerKey = "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

        var qd = QueryDefinition.Init(customerKey);

        var result = qd.Update({
            QueryText: "SELECT TOP 50 SubscriberKey, EmailAddress FROM _Subscribers"
        });

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));
        
    }	

</script>

"OK"

WSProxy

var objectID = "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

var updated_query =  'SELECT TOP 50 SubscriberKey, EmailAddress FROM _Subscribers';

var config = {
    ObjectID: objectID,
    Description: "My updated query",
    CategoryID: 12345,
    TargetType: "DE",
    TargetUpdateType: "Overwrite",
    QueryText: updated_query,
    DataExtensionTarget: {
        Name: "MyDataExtension",
        CustomerKey: "S0M3-GU1D-K3Y-G03SR1G4T-H3R3"
    }
};

var result = api.updateItem("QueryDefinition", config);

<script runat="server">

    Platform.Load("core", "1");

    var api = new Script.Util.WSProxy();
	
	try {

        var name = "MyNewQuery";

        var request = api.retrieve("QueryDefinition", ["ObjectID", "CategoryID"], {
            Property: "Name", 
            SimpleOperator: "equals",
            Value: name 
        });

        var objectID = request.Results[0].ObjectID,
            categoryID = request.Results[0].CategoryID;

        var request = api.retrieve("DataExtension", ["CustomerKey"], {
            Property: "Name",
            SimpleOperator: "equals",
            Value: "MyDataExtension"
        });

        var targetKey = request.Results[0].CustomerKey;

        var updated_query =  'SELECT TOP 50 SubscriberKey, EmailAddress FROM _Subscribers';

        var config = {
            ObjectID: objectID,
            Description: "My updated query",
            CategoryID: categoryID,
            TargetType: "DE",
            TargetUpdateType: "Overwrite",
            QueryText: updated_query,
            DataExtensionTarget: {
                Name: "MyDataExtension",
                CustomerKey: targetKey
            }
        };

        var result = api.updateItem("QueryDefinition", config);

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));
        
    }	

</script>

{
    "Status": "OK",
    "RequestID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "Results": [
        {
            "Object": {
                "Name": null,
                "QueryText": "SELECT TOP 50 SubscriberKey, EmailAddress FROM _Subscribers",
                "TargetType": "DE",
                "TargetUpdateType": "Overwrite",
                "DataExtensionTarget": {
                    "Name": "MyDataExtension",
                    "Description": null,
                    "Keyword": null,
                    "Client": null,
                    "PartnerKey": null,
                    "PartnerProperties": null,
                    "CreatedDate": "0001-01-01T00:00:00.000",
                    "ModifiedDate": null,
                    "ID": 0,
                    "ObjectID": null,
                    "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
                    "Owner": null,
                    "CorrelationID": null,
                    "ObjectState": null,
                    "IsPlatformObject": false
                },
                "ObjectID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
                "FileSpec": null,
                "FileType": null,
                "Status": null,
                "CategoryID": 12345,
                "InteractionObjectID": null,
                "Description": "My updated query",
                "Keyword": null,
                "Client": null,
                "PartnerKey": null,
                "PartnerProperties": null,
                "CreatedDate": "0001-01-01T00:00:00.000",
                "ModifiedDate": null,
                "ID": 0,
                "CustomerKey": null,
                "Owner": null,
                "CorrelationID": null,
                "ObjectState": null,
                "IsPlatformObject": false
            },
            "UpdateResults": null,
            "ParentPropertyName": null,
            "StatusCode": "OK",
            "StatusMessage": "QueryDefinition updated",
            "OrdinalID": 0,
            "ErrorCode": 0,
            "RequestID": null,
            "ConversationID": null,
            "OverallStatusCode": null,
            "RequestType": "Synchronous",
            "ResultType": null,
            "ResultDetailXML": null
        }
    ]
}

REST API

var queryDefinitionId =  "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

var updated_query =  'SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers';

var payload = {
    "name": "MyQuery",
    "key": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "description": "My updated queery",
    "queryText": updated_query
}

var endpoint = restInstanceUrl + "automation/v1/queries/" + queryDefinitionId;

var request = new Script.Util.HttpRequest(endpoint);
    request.emptyContentHandling = 0;
    request.retries = 2;
    request.continueOnError = true;
    request.setHeader("Authorization", "Bearer " + accessToken);
    request.method = "PATCH";
    request.contentType = "application/json";
    request.encoding = "UTF-8";
    request.postData = Stringify(payload);

var results = request.send();

var result = Platform.Function.ParseJSON(String(results.content));

<script runat="server">

    Platform.Load("core", "1");

    var api = new Script.Util.WSProxy();

    var restInstanceUrl = "https://YOUR_SUBDOMAIN.rest.marketingcloudapis.com/",
        accessToken     = "YOUR_REST_API_TOKEN";
	
	try {

        var name = "MyNewQuery";

        var request = api.retrieve("QueryDefinition", ["ObjectID", "CustomerKey"], {
            Property: "Name", 
            SimpleOperator: "equals",
            Value: name 
        });

        var queryDefinitionId = request.Results[0].ObjectID;
        var customerKey = request.Results[0].CustomerKey;

        var updated_query =  'SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers';

        var payload = {
            "name": name,
            "key": customerKey,
            "description": "My updated queery",
            "queryText": updated_query
        }

        var endpoint = restInstanceUrl + "automation/v1/queries/" + queryDefinitionId;

        var request = new Script.Util.HttpRequest(endpoint);
            request.emptyContentHandling = 0;
            request.retries = 2;
            request.continueOnError = true;
            request.setHeader("Authorization", "Bearer " + accessToken);
            request.method = "PATCH";
            request.contentType = "application/json";
            request.encoding = "UTF-8";
            request.postData = Stringify(payload);

        var results = request.send();

        var result = Platform.Function.ParseJSON(String(results.content));

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));
        
    }	

</script>

{
    "queryDefinitionId": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "name": "MyNewQuery",
    "key": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "description": "My updated queery",
    "queryText": "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers",
    "targetName": "MyDataExtension",
    "targetKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "targetId": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "targetDescription": "",
    "createdDate": "2022-12-07T13:05:21.77",
    "modifiedDate": "2022-12-07T13:51:13.5",
    "targetUpdateTypeId": 0,
    "targetUpdateTypeName": "Overwrite",
    "validatedQueryText": "SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\n\r\nINSERT INTO C100000000.[MyDataExtension] ([SubscriberKey], [EmailAddress])\r\nSELECT querydef.[SubscriberKey], querydef.[EmailAddress]\r\nFROM (SELECT TOP 5 SubscriberKey, EmailAddress FROM C100000000._Subscribers) AS querydef \r\nSELECT @rcInsert = @@ROWCOUNT;;\r\n",
    "categoryId": 3125,
    "isFrozen": false
}

WARNING

When the payload is empty or not a valid String, the REST API throws no errors.

Reference

Ressources and references related to the current methods.

Official documentation

SOAP object