Ticket Tag Update Batch - SQL Syntax

V5 Question
1

Good morning!

I have to (correct) update the CPN ticket tag of my first 600 tickets.

I have a table with CPN Ticket Tag corresponding to Ticket Number, like this:

Tkt Nº TT:CPN
101 B001-90
107 B001-92
109 B001-91
110 B001-93
111 B001-98
112 B001-99
113 B001-100
115 B001-104
117 B001-102
118 B001-103
119 B001-101
120 B001-113
121 B001-106
122 B001-105

I would like to make a script that would be executed once (Pressing Test - below the script)

Could you help me to start?

What is the SQL UPDATE syntax to update TT:CPN where TN=x?

Thank you.

2

Ticket tags are stored in json if I remember correctly so would need to use the JSON support added in sql 2016 or make jscript a little more complex.

1 Like
3

I installed SQL Server 2016, basic install, don’t know if JSON is “activated” but I’ve seen a post where it explains how to activate, so should not be a problem for DB.
However I have no experience with JSON in scripts …

4

Me neither, q probably best person.
Used json in scripts but for external API not the SQL feature.

5

I did something similar to this a few days ago. Do you know the table name and column names of the data you want to change?

6

No, that’s is actually my problem: I don’t know where and how are stored the Ticket Tags.

I have the couples (X,Y) where X are the ticket number and Y the ticket tag I want to update.
If somebody could show me the SQL syntax to:

"UPDATE [¿which Table?] SET [¿TicketTag:CPN?]='Y' WHERE [¿TicketNumber?]='X'"

(I doubt it will be that simple)

7

It won’t be that simple.
Ticket tags I think have a single column on ticket table from memory and are stored in json. Ie a json array per ticket with all ticket tags within it

8

I think the table you are looking for is probably ‘Tickets’. The column that holds the ticket tags is ‘TicketTags’ and there is another one “TicketNumber”. You can check this is what you want by using the simple query
SELECT TicketNumber,TicketTags from Tickets
(depending on how many tickets you have you may like to limit the output with a WHERE clause, but that is up to you).

You will now now be able to confirm you have the correct table and you can see the structure of the ticket tags. This will vary depending on what tags you are using, in my case it is [{“TN”:“Discount Rate”,“TT”:0,“TV”:“10”}], you should see something similar. By comparing the tags on different rows you can see what is fixed by samba and what is your data. You can now modify your data making sure you don’t alter the structure of the tag and update the database using

UPDATE Tickets SET TicketTags = ‘add your modified ticket tag here with the apostrophes’ WHERE TicketNumber = ‘add your ticket number here with the apostrophes’;

Once you have tested this with one ticket and checked you get the desired result, as you have 600 rows to update with a repetitive query I suggest you build the text of the query in Excel in bits then use the & function to join the bits to give you 600 very similar queries. You can then transfer that to whatever you are using to run the query (SSMS).

Whilst the above process works, I would add the proviso that messing with the database in this way is a potentially dangerous exercise, so make sure you take a backup before you start

1 Like