CODEWITHSUNDEEP
sqlsql-serverjoinsubquery
lije
lije

Reputation: 420

Changing subquery to join to update a table

I was trying to convert the subquery to join in the update statement in order to pursuit of tuning it so as to prevent a deadlock. I was trying in vain to come to the exact update as I've to take the Top 1.

-- create associate table and insert sample data 

CREATE TABLE [dbo].[Associate](
    [pre_plan_id] [smallint] NULL,
    [pre_type_id] [smallint] NULL,
    [associate_id] [smallint] NOT NULL,
    [deleted] [bit] NOT NULL
)  

INSERT INTO Associate 
VALUES 
(NULL,  NULL,   -32768, 0),
(NULL,  NULL,   2,  1),
(NULL,  NULL,   3,  0)

-- create visit table and insert sample data

CREATE TABLE [dbo].[Visit](
    [type_id] [smallint] NOT NULL,
    [plan_id] [smallint] NOT NULL,
    [associate_id] [smallint] NOT NULL,
    [time_in] [smalldatetime] NOT NULL
) 
INSERT INTO Visit 
VALUES
(390,   31, 3109,   '2009-09-02'),
(304,   32, 3109,   '2010-02-05'),
(388,   31, 3109,   '2010-09-24')

--- the update statement 

UPDATE Associate SET pre_plan_id = 
      (SELECT TOP 1 plan_id 
         FROM Visit  
        WHERE associate_id = Associate.associate_id 
          AND time_in > 90
        GROUP BY plan_id 
        ORDER BY Count(*) DESC)
 WHERE deleted = 0

Can anyone help me with this? Thanks!

Upvotes: 0

Views: 58

Answers (1)

Caius Jard
Caius Jard

Reputation: 74625

Give this one a try:

UPDATE a 
SET a.pre_plan_id = z.plan_id
FROM
  associate a
  INNER JOIN
  (
    SELECT associate_id, plan_id 
    FROM
    (
      SELECT associate_id, plan_id, ROW_NUMBER() OVER(PARTITION BY associate_id ORDER BY ctr DESC) rown
      FROM (SELECT associate_id, plan_id, count(*) as ctr FROM visit WHERE time_in > 90 GROUP BY associate_id, plan_id) x
    ) y
    WHERE rown = 1
  ) z
  ON z.associate_id = a.associate_id
WHERE deleted = 0

It's got 3 subqueries only because I can't remember if it's possible to use a grouped count(*) in a window function.. If it is, this may work:

UPDATE a 
SET a.pre_plan_id = z.plan_id
FROM
  associate a
  INNER JOIN
  (
    SELECT associate_id, plan_id 
    FROM
    (
      SELECT associate_id, plan_id, ROW_NUMBER() OVER(PARTITION BY associate_id ORDER BY COUNT(*) DESC) rown
      FROM visit 
      WHERE time_in > 90 
      GROUP BY associate_id, plan_id
    ) y
    WHERE rown = 1
  ) z
  ON z.associate_id = a.associate_id
WHERE deleted = 0

Or even this, shorter form:

UPDATE a 
SET a.pre_plan_id = z.plan_id
FROM
  associate a
  INNER JOIN
  (
      SELECT associate_id, plan_id, ROW_NUMBER() OVER(PARTITION BY associate_id ORDER BY COUNT(*) DESC) rown
      FROM visit 
      WHERE time_in > 90 
      GROUP BY associate_id, plan_id
  ) z
  ON z.associate_id = a.associate_id AND z.rown = 1
WHERE deleted = 0

It seems you want to count up the visits and select the one with the highest number of occurrences of plan_id and then use this to populate pre_plan_id. By grouping and getting the counts, then doing a row_number() over the counts descending it means the highest count will be 1, which we can filter on and pluck out the associated plan_id as the pre_plan_id

Upvotes: 2

Related Questions