Post reply

DELETE data from table, WHERE a field in JSON column

  • July 26, 2018 at 2:18 am

    #373810

    Hi,

    I have data in table "FileTable" with a json field, in that field there are values:
    { "File":"Daily", "FileNumber":01, "FileType":"DO"}
    { "File":"Monthly", "FileNumber":02, "FileType":"DO"}

    I want to Delete data from FileTable but where "FileType" = "DO"

    Please help.

  • July 26, 2018 at 2:41 am

    #1999044

    Hi,

    As I tried to replicate your JSON column values in one of my tables, but there is a flaw here. You will need to enclose the FileNumber part with double-quote as well (e.g. { "File":"Daily", "FileNumber":"01", "FileType":"DO"}).

    Otherwise it will throw error.

    Then you can use the following query to delete:
    DELETE dbo.<YourTableName> WHERE JSON_VALUE(<YourColumnName>, '$.FileType') = 'DO'

    Please let me know.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply