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

Help me turn coffee into code

This website is provided to you free of charge. However, a lot of time and effort are spent to write, test and mainain the code. Please consider supporting my work by buying me a cup of coffee.

Buy me a coffee