• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

Update field in order

I have one table which contains one or multiple entries with dates.
I have another table that has ONE field for ONE date.

I am running a query that will take the date from the first table and copy it over to the second table. When there is only one record on the first table its a one to one relation, so no issue there, but if there is more than one record on the first table when I run the queries then the second table first is updated with the first value and then when the query continues it is updated with the second value.

I need to copy the most recent date, which sometimes may be the first record and not the second.

For example. Let's say they are school dates. The first table has the dates in which a person graduated, so they may have 01/01/2000 and then 05/03/2003 and then 09/18/1998
The second table has a field to have the most recent graduation date. I hope I start to make sense, you can see where this is going.

Below are all my scripts, which will copy records from table A to table B, they need to match a certain ID, but the first scripts may have a more recent date and the scripts that follow will overwrite whatever the scripts at the top did. So I need to somehow modify this queries and perhaps have one query that will update the target field with the most recent of the dates on table 1.

table 1 is: BlueDotAdditionalDocs
date field 1 is: a.Filedon

table 2 is: Users
date field 2 is: prioritydate

-- copy exp date of FB-2A Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 593




-- copy exp date of FB-2B Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 608



-- copy exp date of FB-3 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 609



-- copy exp date of FB-4 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 610



-- copy exp date of EB-1 OR Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 611



-- copy exp date of EB-1 EA Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 612



-- copy exp date of EB-1 MM Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 613




-- copy exp date of EB-2 NIW Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 614




-- copy exp date of EB-2 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 615




-- copy exp date of EB-3 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 616




-- copy exp date of EB-4 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 617




-- copy exp date of EB-5 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 618




-- copy exp date of Sched A Workers Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 619




-- copy exp date of Other Workers Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 620




-- copy exp date of Religious Workers Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 621



-- copy exp date of Targeted EA/RC Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 622



-- copy exp date of FB-1 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 624

Open in new window


 I tried the query below but I am not sure if this idea would work and also I get an error in the order clause.

-- Update dates by most recent -- 

-- Update dates by most recent -- 

UPDATE  u
SET     prioritydate = a.Filedon
FROM    Users u
        JOIN BlueDotAdditionalDocs a ON u.UserId = a.UserId
WHERE   a.docsection = 'contact'
        AND a.Docurrent = 1 AND a.filedon IS NOT NULL
        AND ( a.CatalogId = 593
              OR a.CatalogId = 608
              OR a.CatalogId = 609
              OR a.CatalogId = 610
              OR a.CatalogId = 611
              OR a.CatalogId = 612
              OR a.CatalogId = 613
              OR a.CatalogId = 614
              OR a.CatalogId = 615
              OR a.CatalogId = 616
              OR a.CatalogId = 617
              OR a.CatalogId = 618
              OR a.CatalogId = 619
              OR a.CatalogId = 620
              OR a.CatalogId = 621
              OR a.CatalogId = 622
              OR a.CatalogId = 624
            )
			
			ORDER BY a.filedon ASC;

Open in new window

0
Aleks
Asked:
Aleks
  • 10
  • 7
  • 4
1 Solution
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
I am trying to work out something for you. What version of SQL Server are you using (2008 or 2012 and above)?
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Here's a prototype that has been tested on SQL 2008 and 2012.

Basically, the crux of the approach is the use of the CTE and the ROW_NUMBER() windowing function. What I ask the ROW_NUMBER() to do is to number the rows in the descending order of the Catalog Id for each user. Idea is to assign a row number of 1 to the max. catalog Id available for a particular user.

Let me know if it works, or if you need any further assistance.

DECLARE @users TABLE (UserId       INT  NOT NULL,
                      prioritydate DATE     NULL
                     );

DECLARE @BlueDotAdditionalDocs TABLE (Id         INT         NOT NULL IDENTITY (1,1),
                                      UserId     INT         NOT NULL,
                                      CatalogId  INT             NULL,
                                      Docurrent  BIT             NULL,
                                      docsection VARCHAR(20)     NULL,
                                      filedon    DATE            NULL
                                     );

--Some Users
INSERT INTO @users (UserId)
VALUES (1), (2), (3), (5), (8);

--Some Documents, valid and invalid
INSERT @BlueDotAdditionalDocs (UserId, CatalogId, Docurrent, docsection, filedon)
VALUES (1, 593, 1,   'contact', '2016-01-01'), --Effective
       (1, 609, 0,   'contact', '2016-01-25'),
       (1, 618, 1, 'something', '2016-02-02'),
       (2, 593, 1,   'contact', '2016-02-01'),
       (2, 609, 0,   'contact', '2016-02-25'),
       (2, 618, 1,   'contact', '2016-03-02'), --Effective
       (3, 593, 1,   'contact', '2016-02-10'),
       (3, 609, 1,   'contact', '2016-02-15'),
       (3, 618, 1,   'contact', '2016-03-20'), --Effective
       (5, 624, 1,   'contact', '2016-08-10'), --Effective (Special case 01)
       (5, 617, 1,   'contact', '2016-06-15'),
       (5, 610, 1,   'contact', '2016-05-20'),
       (8, 624, 1,   'contact', '2016-08-10'), --Effective (Special case 02)
       (8, 617, 1,   'contact', '2016-09-15'), --NOTE: Ignored due to lower CatalogId
       (8, 610, 1,   'contact', '2016-09-10');

/* Debug Point */
--SELECT a.UserId,
--        a.CatalogId,
--        a.Docurrent,
--        a.docsection,
--        a.filedon,
--        ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
--FROM @BlueDotAdditionalDocs AS a
--WHERE a.docsection = 'contact'
--    AND a.Docurrent = 1 ;


--Perform the update
;WITH BlueDotAdditionalDocsByPriority (UserId,
                                       filedon,
                                       PriorityOrder
                                      )
AS (
    SELECT a.UserId,
           --a.CatalogId,
           --a.Docurrent,
           --a.docsection,
           a.filedon,
           ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
    FROM @BlueDotAdditionalDocs AS a
    WHERE a.docsection = 'contact'
      AND a.Docurrent = 1
   )
UPDATE u
SET u.prioritydate = bdap.filedon
FROM @users AS u
INNER JOIN BlueDotAdditionalDocsByPriority AS bdap ON u.UserId = bdap.UserId
WHERE bdap.PriorityOrder = 1;

--Check the update
SELECT u.UserId, 
       u.prioritydate
FROM @users AS u;

/* RESULTS
UserId      prioritydate
----------- ------------
1           2016-01-01
2           2016-03-02
3           2016-03-20
5           2016-08-10
8           2016-08-10
*/

Open in new window

0
 
AleksAuthor Commented:
I am using 2008R2. I will test this. Seems more complex than I previously anticipated :$
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Thanks. Actually a simpler approach just came to mind. I'm on my evening commute, but will prototype it later this evening and let you know.
0
 
AleksAuthor Commented:
Thx
0
 
Scott PletcherSenior DBACommented:
UPDATE u
SET prioritydate = b.Filedon
FROM users u
CROSS APPLY (
    SELECT TOP (1) *
    FROM BlueDotAdditionalDocs bdad
    WHERE bdad.userid = u.userid
    ORDER BY bdad.Filedon DESC
) AS b
WHERE a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 593

Btw, an index on  BlueDotAdditionalDocs keyed on ( userid, Filedon ) could speed up this query considerably.
0
 
AleksAuthor Commented:
How about all the other IDs?
This is a one time data import.
0
 
Scott PletcherSenior DBACommented:
Sorry, I should have explicitly stated it, but you should specify a join or a list of ids in the CROSS APPLY query.  I also didn't have time before to provide specific details.  But now I can:

IF OBJECT_ID('tempdb.dbo.#doc_criteria') IS NOT NULL
    DROP TABLE #doc_criteria
SELECT TOP (0) docsection, Docurrent, CatalogID, CAST('' AS varchar(200)) AS description
INTO #doc_critera
FROM BlueDotAdditionalDocs

CREATE UNIQUE CLUSTERED INDEX doc_criteria__CL ON #doc_criteria ( docsection, CatalogID, Docurrent ) WITH ( FILLFACTOR = 100 );

INSERT INTO #doc_criteria
VALUES ('contact', 1, 593, 'FB-2A Priority Date'),
       ('contact', 1, 608, 'FB-2B Priority Date'),
       ('contact', 1, 609, 'FB-3 Priority Date'),
       ('contact', 1, 610, 'FB-4 Priority Date'),
       ('contact', 1, 611, 'EB-1 OR Priority Date'),
       ('contact', 1, 612, 'EB-1 EA Priority Date') --,...add_rest_of_valid_doc_values_here...

UPDATE u
SET prioritydate = b.Filedon
FROM users u
CROSS APPLY (
    SELECT TOP (1) *
    FROM BlueDotAdditionalDocs bdad
    INNER JOIN #doc_criteria dc ON
        dc.docsection = bdad.docsection AND
        dc.Docurrent = bdad.Docurrent AND
        dc.CatalogID = bdad.CatalogID
    WHERE bdad = u.userid
    ORDER BY bdad.Filedon DESC
) AS b
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
The simpler approach I was talking about was using trivial/classic aggregation operators to identify the "filedon" date in effect for any given user. Once this set is available, updating the users table with the date takes a simple join.

The script below has both the options in it. It uses temporary objects and hence you can run it on your development environment as much as you like - with your custom test data to cross-verify.

DECLARE @users TABLE (UserId       INT  NOT NULL,
                      prioritydate DATE     NULL
                     );

DECLARE @BlueDotAdditionalDocs TABLE (Id         INT         NOT NULL IDENTITY (1,1),
                                      UserId     INT         NOT NULL,
                                      CatalogId  INT             NULL,
                                      Docurrent  BIT             NULL,
                                      docsection VARCHAR(20)     NULL,
                                      filedon    DATE            NULL
                                     );

--Some Users
INSERT INTO @users (UserId)
VALUES (1), (2), (3), (5), (8);

--Some Documents, valid and invalid
INSERT @BlueDotAdditionalDocs (UserId, CatalogId, Docurrent, docsection, filedon)
VALUES (1, 593, 1,   'contact', '2016-01-01'), --Effective
       (1, 609, 0,   'contact', '2016-01-25'),
       (1, 618, 1, 'something', '2016-02-02'),
       (2, 593, 1,   'contact', '2016-02-01'),
       (2, 609, 0,   'contact', '2016-02-25'),
       (2, 618, 1,   'contact', '2016-03-02'), --Effective
       (3, 593, 1,   'contact', '2016-02-10'),
       (3, 609, 1,   'contact', '2016-02-15'),
       (3, 618, 1,   'contact', '2016-03-20'), --Effective
       (5, 624, 1,   'contact', '2016-08-10'), --Effective (Special case 01)
       (5, 617, 1,   'contact', '2016-06-15'),
       (5, 610, 1,   'contact', '2016-05-20'),
       (8, 624, 1,   'contact', '2016-08-10'), --Effective (Special case 02)
       (8, 617, 1,   'contact', '2016-09-15'), --NOTE: Ignored due to lower CatalogId
       (8, 610, 1,   'contact', '2016-09-10');


/** OPTION 01: Using Windowing Function to identify effective "filedon" date **/

/* Debug Point */
--SELECT a.UserId,
--        a.CatalogId,
--        a.Docurrent,
--        a.docsection,
--        a.filedon,
--        ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
--FROM @BlueDotAdditionalDocs AS a
--WHERE a.docsection = 'contact'
--    AND a.Docurrent = 1 ;


--Perform the update
;WITH BlueDotAdditionalDocsByPriority (UserId,
                                       filedon,
                                       PriorityOrder
                                      )
AS (
    SELECT a.UserId,
           --a.CatalogId,
           --a.Docurrent,
           --a.docsection,
           a.filedon,
           ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
    FROM @BlueDotAdditionalDocs AS a
    WHERE a.docsection = 'contact'
      AND a.Docurrent = 1
   )
UPDATE u
SET u.prioritydate = bdap.filedon
FROM @users AS u
INNER JOIN BlueDotAdditionalDocsByPriority AS bdap ON u.UserId = bdap.UserId
WHERE bdap.PriorityOrder = 1;

--Check the update
SELECT u.UserId, 
       u.prioritydate
FROM @users AS u;

/* RESULTS
UserId      prioritydate
----------- ------------
1           2016-01-01
2           2016-03-02
3           2016-03-20
5           2016-08-10
8           2016-08-10
*/


/** OPTION 02: Use classic aggregations to identify effective "filedon" date **/
--Reset the test data
UPDATE u
SET u.prioritydate = NULL
FROM @users AS u;

;WITH LastCatalogInEffectByUser (UserId, CatalogIdInEffect)
AS (SELECT a.UserId,
		   MAX(a.CatalogId) AS CatalogIdInEffect
	FROM @BlueDotAdditionalDocs AS a
	WHERE a.docsection = 'contact'
	  AND a.Docurrent = 1
	GROUP BY a.UserId
	)
UPDATE u
SET u.prioritydate = a.filedon
FROM @users AS u
INNER JOIN @BlueDotAdditionalDocs AS a ON u.UserId = a.UserId
INNER JOIN LastCatalogInEffectByUser AS lceu ON a.UserId = lceu.UserId
                                            AND a.CatalogId = lceu.CatalogIdInEffect;


--Check the update
SELECT u.UserId, 
       u.prioritydate
FROM @users AS u;

/* RESULTS
UserId      prioritydate
----------- ------------
1           2016-01-01
2           2016-03-02
3           2016-03-20
5           2016-08-10
8           2016-08-10
*/

Open in new window

0
 
AleksAuthor Commented:
I ran the above and got a lot of errors, some had hardcoded numbers so I am a bit confused. Is there a way to clean it up so that I can just run the part of the query that should copy the dates?
0
 
AleksAuthor Commented:
From Scotts code it doesn't account for all the other IDs

UPDATE u
SET prioritydate = b.Filedon
FROM users u
CROSS APPLY (
    SELECT TOP (1) *
    FROM BlueDotAdditionalDocs bdad
    WHERE bdad.userid = u.userid
    ORDER BY bdad.Filedon DESC
) AS b
WHERE a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 593

Open in new window

0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
If possible, can you please share the errors?
0
 
AleksAuthor Commented:
Well there is code that inserts data you entered, such as:


--Some Documents, valid and invalid
INSERT @BlueDotAdditionalDocs (UserId, CatalogId, Docurrent, docsection, filedon)
VALUES (1, 593, 1,   'contact', '2016-01-01'), --Effective
       (1, 609, 0,   'contact', '2016-01-25'),
       (1, 618, 1, 'something', '2016-02-02'),
       (2, 593, 1,   'contact', '2016-02-01'),
       (2, 609, 0,   'contact', '2016-02-25'),
       (2, 618, 1,   'contact', '2016-03-02'), --Effective

Those are not values from my database so I did not run a lot of stuff in that script.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Yes, the script I had is intended to be a demo script that needs to be adapted to your database design. Instead of tables, I used table variables so that I could populate them with test data and run some tests.

In the queries below, I have removed the use of table variables and used physical tables (with names from your original example in the question).

Option 01: Using Windowing functions:
/** OPTION 01: Using Windowing Function to identify effective "filedon" date **/
--Perform the update
;WITH BlueDotAdditionalDocsByPriority (UserId,
                                       filedon,
                                       PriorityOrder
                                      )
AS (
    SELECT a.UserId,
           --a.CatalogId,
           --a.Docurrent,
           --a.docsection,
           a.filedon,
           ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
    FROM dbo.BlueDotAdditionalDocs AS a
    WHERE a.docsection = 'contact'
      AND a.Docurrent = 1
   )
UPDATE u
SET u.prioritydate = bdap.filedon
FROM dbo.users AS u
INNER JOIN BlueDotAdditionalDocsByPriority AS bdap ON u.UserId = bdap.UserId
WHERE bdap.PriorityOrder = 1;

Open in new window


Option 02: Conventional method to identify effective "filedon" date:
;WITH LastCatalogInEffectByUser (UserId, CatalogIdInEffect)
AS (SELECT a.UserId,
		   MAX(a.CatalogId) AS CatalogIdInEffect
	FROM dbo.BlueDotAdditionalDocs AS a
	WHERE a.docsection = 'contact'
	  AND a.Docurrent = 1
	GROUP BY a.UserId
	)
UPDATE u
SET u.prioritydate = a.filedon
FROM dbo.users AS u
INNER JOIN dbo.BlueDotAdditionalDocs AS a ON u.UserId = a.UserId
INNER JOIN LastCatalogInEffectByUser AS lceu ON a.UserId = lceu.UserId
                                            AND a.CatalogId = lceu.CatalogIdInEffect;

Open in new window

0
 
AleksAuthor Commented:
I dont see the ID's of the specific catalogs that should be used. there are other ID's that are not relevant.

This is my initial query which includes ONLY the IDs that should be compared.

UPDATE  u
SET     prioritydate = a.Filedon
FROM    Users u
        JOIN BlueDotAdditionalDocs a ON u.UserId = a.UserId
WHERE   a.docsection = 'contact'
        AND a.Docurrent = 1 AND a.filedon IS NOT NULL
        AND ( a.CatalogId = 593
              OR a.CatalogId = 608
              OR a.CatalogId = 609
              OR a.CatalogId = 610
              OR a.CatalogId = 611
              OR a.CatalogId = 612
              OR a.CatalogId = 613
              OR a.CatalogId = 614
              OR a.CatalogId = 615
              OR a.CatalogId = 616
              OR a.CatalogId = 617
              OR a.CatalogId = 618
              OR a.CatalogId = 619
              OR a.CatalogId = 620
              OR a.CatalogId = 621
              OR a.CatalogId = 622
              OR a.CatalogId = 624
            )
			
			ORDER BY a.filedon DESC;

Open in new window

0
 
Scott PletcherSenior DBACommented:
From Scotts code it doesn't account for all the other IDs

Look at the updated code I posted.  If you want to use a list of conditions, you'll have to specify all of them.  To me, the best and most flexible way to do that is via a table, rather than hard-coding a list of values.
0
 
AleksAuthor Commented:
The table has more IDs that's why i specified them
0
 
AleksAuthor Commented:
?
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Not being familiar with your system, I had left room for changes as necessary. Now that I know you have additional catalogs, here are the updated options with specific CatalogIds added to the WHERE clause.

Option 01:
/** OPTION 01: Using Windowing Function to identify effective "filedon" date **/
--Perform the update
;WITH BlueDotAdditionalDocsByPriority (UserId,
                                       filedon,
                                       PriorityOrder
                                      )
AS (
    SELECT a.UserId,
           --a.CatalogId,
           --a.Docurrent,
           --a.docsection,
           a.filedon,
           ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
    FROM dbo.BlueDotAdditionalDocs AS a
    WHERE a.docsection = 'contact'
      AND a.Docurrent = 1
      AND a.filedon IS NOT NULL
      AND a.CatalogId IN (593, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 624)
   )
UPDATE u
SET u.prioritydate = bdap.filedon
FROM dbo.users AS u
INNER JOIN BlueDotAdditionalDocsByPriority AS bdap ON u.UserId = bdap.UserId
WHERE bdap.PriorityOrder = 1;

Open in new window


Option 02:
;WITH LastCatalogInEffectByUser (UserId, CatalogIdInEffect)
AS (SELECT a.UserId,
		   MAX(a.CatalogId) AS CatalogIdInEffect
	FROM dbo.BlueDotAdditionalDocs AS a
	WHERE a.docsection = 'contact'
	  AND a.Docurrent = 1
      AND a.filedon IS NOT NULL
      AND a.CatalogId IN (593, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 624)
	GROUP BY a.UserId
	)
UPDATE u
SET u.prioritydate = a.filedon
FROM dbo.users AS u
INNER JOIN dbo.BlueDotAdditionalDocs AS a ON u.UserId = a.UserId
INNER JOIN LastCatalogInEffectByUser AS lceu ON a.UserId = lceu.UserId
                                            AND a.CatalogId = lceu.CatalogIdInEffect;

Open in new window

0
 
AleksAuthor Commented:
Used the last query. Seemed to work with no errors. User will check on results tomorrow.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now