validated
Update a Query ActivityLearn 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.