CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-update
blackbishop
blackbishop

Reputation: 32650

Reference column from main update query in subquery

I have a table UserLOG (>5M rows) with a column LOG_PARAMETERS that contains a Json string and others columns (that I named LOG_param1 to LOG_param9 here). I am using the Phil Factor TSQL Function to extract parameters from the Json and update my table.

Here is my table creation script:

CREATE TABLE User_LOG(
    [LOG_Id] [int] IDENTITY(1,1) NOT NULL,
    [LOG_PARAMETERS] [nvarchar](500) NULL,
    [LOG_DATE] [varchar](25) NULL,
    [LOG_param1] [uniqueidentifier] NULL,
    [LOG_param2] [uniqueidentifier] NULL,
    [LOG_param3] [uniqueidentifier] NULL,
    [LOG_param4] [uniqueidentifier] NULL,
    [LOG_param5] [uniqueidentifier] NULL,
    [LOG_param6] [uniqueidentifier] NULL,
    [LOG_param7] [varchar](25) NULL,
    [LOG_param8] [int] NULL,
    [LOG_param9] [smallint] NULL,
    CONSTRAINT [PK_UserLOG] PRIMARY KEY CLUSTERED ([LOG_Id] ASC)
)

Some data:

INSERT INTO dbo.User_LOG ([LOG_PARAMETERS]) 
   values ('{"LOG_param7": "2015-06-06T17:24:06.000Z", "LOG_param1": "04162673-90df-495f-9691-32c063b78e80", "LOG_param2": "2230f23e-83b6-46b8-aa81-fefce20efdf0", "LOG_param3": "7aa411a0-c265-4d8f-896a-4b2707c4086e"}'),
   ('{"LOG_param7": "2015-06-06T17:24:06.000Z", "LOG_param1": "04462673-90df-495f-9691-32c063b88e80", "LOG_param2": "2230f23e-85b6-46b8-aa81-fefce20efdf0", "LOG_param3": "7aa419a0-c265-4d8f-896a-4b2707c4086e"}'),
   ('{"LOG_param7": "2015-06-06T17:24:06.000Z", "LOG_param1": "04162673-90df-495f-9691-32c063b68e80", "LOG_param2": "2260f23e-83b6-46b8-ba81-fefce20efdf0", "LOG_param3": "7aa511a0-c265-4d8f-896a-4b2707c4086e"}'),
   ('{"LOG_param7": "2015-06-06T17:24:06.000Z", "LOG_param1": "04152673-90df-495f-9691-32c063b98e80", "LOG_param2": "2230f23e-82b6-46b8-va81-fefce20efdf0", "LOG_param3": "7aa411a0-c265-4d8f-893a-4b2707c4086e"}')

I am trying to update this table by using the following query:

UPDATE  User_LOG 
SET  LOG_param1 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param1])
    ,LOG_param2 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param2])
    ,LOG_param3 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param3])
    ,LOG_param4 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param4])
    ,LOG_param5 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param5])
    ,LOG_param6 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param6])
    ,LOG_param7 = j.[LOG_param7]
    ,LOG_param8 = CAST(j.[LOG_param8] AS INT)
    ,LOG_param9 = CAST(j.[LOG_param9] AS SMALLINT)
FROM (SELECT 
         max(case when name='LOG_param1' then value else null end) as [LOG_param1],
         max(case when name='LOG_param2' then value else null end) as [LOG_param2],
         max(case when name='LOG_param3' then value else null end) as [LOG_param3],
         max(case when name='LOG_param4' then value else null end) as [LOG_param4],
         max(case when name='LOG_param5' then value else null end) as [LOG_param5],
         max(case when name='LOG_param6' then value else null end) as [LOG_param6],
         max(case when name='LOG_param7' then value else null end) as [LOG_param7],
         max(case when name='LOG_param8' then value else null end) as [LOG_param8],
         max(case when name='LOG_param9' then value else null end) as [LOG_param9]
     FROM temp.parseJSON(User_LOG.LOG_PARAMETERS) /* I want to reference the LOG_PARAMETERS column here */
     ) j

I update all param columns from a subquery in which I extract the Json parameters. I need to pass the LOG_PARAMETERS column to the function in subquery but I'm getting this error:

Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "User_LOG.LOG_PARAMETERS" could not be bound.

I am not even sure if this is possible or not. Is there a way to achieve this update?

Upvotes: 1

Views: 72

Answers (1)

Serg
Serg

Reputation: 22811

Use APPLY , kind of

UPDATE  User_LOG 
SET  LOG_param1 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param1])
    ,LOG_param2 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param2])   
    ,LOG_param3 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param3])
    ,LOG_param4 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param4])
    ,LOG_param5 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param5])
    ,LOG_param6 = CONVERT(UNIQUEIDENTIFIER, j.[LOG_param6])
    ,LOG_param7 = j.[LOG_param7]

    ,LOG_param8 = CAST(j.[LOG_param8] AS INT)
    ,LOG_param9 = CAST(j.[LOG_param9] AS SMALLINT)
FROM User_LOG
CROSS APPLY (SELECT 
         max(case when name='LOG_param1' then Stringvalue else null end) as [LOG_param1],
         max(case when name='LOG_param2' then Stringvalue else null end) as [LOG_param2],
         max(case when name='LOG_param3' then Stringvalue else null end) as [LOG_param3],
         max(case when name='LOG_param4' then Stringvalue else null end) as [LOG_param4],
         max(case when name='LOG_param5' then Stringvalue else null end) as [LOG_param5],
         max(case when name='LOG_param6' then Stringvalue else null end) as [LOG_param6],
         max(case when name='LOG_param7' then Stringvalue else null end) as [LOG_param7],
         max(case when name='LOG_param8' then Stringvalue else null end) as [LOG_param8],
         max(case when name='LOG_param9' then Stringvalue else null end) as [LOG_param9]
     FROM parseJSON(LOG_PARAMETERS) /* I want to reference the LOG_PARAMETERS column here */
     ) j;

Upvotes: 1

Related Questions