Many to one TOP N UPDATE troubles
-
Hoping the gurus here can offer some advise here. I need to update a field ( Age ) in Table_1 based on a UserID hit in Table_2. I need to have the rows in Table_2 sorted by RecDate ( DESC ) to grab the most recent Age and if the Age is blank, grab the next rec in line. If all are blank simply write nothing to Table_1.
Table_1==========
UserID Lastname Firstname Age
123 Smith Joe
124 Jones Linda
Table_2=========
UserID RecDate Age
123 2007-08-12
123 2010-11-02 92
123 2008-09-22 90
124 2007-09-22 67
124 2008-09-22
124 2010-09-22
124 2009-09-22 68
What I have so far is ( minus the blank condition )...
UPDATE a
SET a.Age =
(
SELECT TOP(1) b.Age
FROM Table_2 b
WHERE a.UserID = b.UserID
ORDER BY b.RecDate DESC
)
FROM Table_1 a
This, so far, does not give me what I'm looking for.
Also, I'm on SQL Server 2008.
Appreciate any guidance.
-
You haven't provided sample data, but something like this should do the job:
;WITH Latest_Age AS (
SELECT UserID, Age, ROW_NUMBER() OVER (PARTITION BY UserID, ORDER BY RecDate DESC) rn
FROM Table_2
WHERE Age is NOT NULL --Did you mean "blank" or NULL? Switch this out for ='' if required
)
UPDATE a
SET Age=b.age
FROM Table_1 a
INNER JOIN Latest_Age b ON a.UserID=b.UserID
WHERE b.rn=1
Obviously untested and just a guide as we don't have sample data
-
i think this is correct
UPDATE a
SET a.Age = b.age
from table_1 a left outer join (
SELECT userid,age from table_2 x where recdate=(select MAX(recdate) from table_2 where userid=x.userid and age is not null)
) b on a.userid=b.userid
MVDBA
-
michael vessey (5/18/2012)
i think this is correctUPDATE a
SET a.Age = b.age
from table_1 a left outer join (
SELECT userid,age from table_2 x where recdate=(select MAX(recdate) from table_2 where userid=x.userid and age is not null)
) b on a.userid=b.userid
Worked perfectly. Cheers Mike!
-
AlAndrew (5/18/2012)
michael vessey (5/18/2012)
i think this is correctUPDATE a
SET a.Age = b.age
from table_1 a left outer join (
SELECT userid,age from table_2 x where recdate=(select MAX(recdate) from table_2 where userid=x.userid and age is not null)
) b on a.userid=b.userid
Worked perfectly. Cheers Mike!
If you use this approach, you'll probably want to change that to an INNER JOIN from what I understood of your requirement. Otherwise you're either:
1) Overwriting any existing ages with NULL if they don't have an age in Table 2 rather than "doing nothing"
2) If there are no existing ages that you have to worry about, you're still unnecessarily performing an update on the non-matching rows if they don't need to have an update applied on them.
-
HowardW (5/18/2012)
AlAndrew (5/18/2012)
michael vessey (5/18/2012)
i think this is correctUPDATE a
SET a.Age = b.age
from table_1 a left outer join (
SELECT userid,age from table_2 x where recdate=(select MAX(recdate) from table_2 where userid=x.userid and age is not null)
) b on a.userid=b.userid
Worked perfectly. Cheers Mike!
If you use this approach, you'll probably want to change that to an INNER JOIN from what I understood of your requirement. Otherwise you're either:
1) Overwriting any existing ages with NULL if they don't have an age in Table 2 rather than "doing nothing"
2) If there are no existing ages that you have to worry about, you're still unnecessarily performing an update on the non-matching rows if they don't need to have an update applied on them.
yes - agreed - i read the words "write nothing" as "write NULL" - my mistake
MVDBA
-
michael vessey (5/18/2012)
HowardW (5/18/2012)
AlAndrew (5/18/2012)
michael vessey (5/18/2012)
i think this is correctUPDATE a
SET a.Age = b.age
from table_1 a left outer join (
SELECT userid,age from table_2 x where recdate=(select MAX(recdate) from table_2 where userid=x.userid and age is not null)
) b on a.userid=b.userid
Worked perfectly. Cheers Mike!
If you use this approach, you'll probably want to change that to an INNER JOIN from what I understood of your requirement. Otherwise you're either:
1) Overwriting any existing ages with NULL if they don't have an age in Table 2 rather than "doing nothing"
2) If there are no existing ages that you have to worry about, you're still unnecessarily performing an update on the non-matching rows if they don't need to have an update applied on them.
yes - agreed - i read the words "write nothing" as "write NULL" - my mistake
This is my final product ..
UPDATE aSET a.Age = b.Age
from Table_1 a join (
SELECT userid, x.Age
from Table_2 x
where recdate=(select MAX(recdate) from Table_2 where userid=x.userid and age is not NULL)
AND x.Age IS NOT NULL AND x.Age > '0'
) b on a.userid=b.userid
Thanks to Mike and HowardW. Much appreciated!
-
I debated on leaving this alone since the OP is good to go but I don't want to see others confused by this. Although, if you're going to boilerplate I should probably just boilerplate the quote responses.
CELKO (5/18/2012)
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are.Because for a simple question that's sooo desperately required...
Why are you so concerned with it; so that you can criticise even more then them asking a simple question? I'm pretty sure that if I was concerned about overly-anal ivory-tower nitpickers foaming at the mouth about me using Money instead of Decimal 19,4 that I'd avoid it too if it wasn't absolutely necessary. That's the polite version.
If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats.
Link to free reference please.
Code should be in Standard SQL as much as possible and not local dialect.
SQL Server forum, get over it.
After 3-4 decades of SQL and RDBMS, I can spot bad code and a bad mindset.
Answer one of your own questions with your own answers, and see if you can spot the bad mindset we all see with you.
You are worse than the bad examples in my books. Please stop programming until you are minimally competent and have some manners.
Pot, Kettle... Did you have a preference for the remaining conversation or shall we flip a coin?
Age is both vague and dynamic. Birth date is a fact from which age is computed (Western or Eastern method).
The only piece of your entire post that has any redeeming value whatsoever. However, not knowing the source data or purpose, we don't know if that's even available. For examples, most surveys just ask for an age, not a birthday.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
CELKO (5/18/2012)
Please stop programming until you are minimally competent and have some manners.BWAAA-HAAA!!! The pot calls the kettle black.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Possibly a small improvement:
CREATE TABLE #TableA
(
UserID integer NOT NULL,
LastName nvarchar(50) NOT NULL,
FirstName nvarchar(50) NOT NULL,
Age tinyint NULL,
CONSTRAINT PK_#TableA
PRIMARY KEY CLUSTERED (UserID)
);
INSERT #TableA
(UserID, LastName, FirstName, Age)
VALUES
(123, N'Smith', N'Joe', NULL),
(124, N'Jones', N'Linda', NULL);
CREATE TABLE #TableB
(
UserID integer NOT NULL,
RecDate date NOT NULL,
Age tinyint NULL,
CONSTRAINT PK_#TableB
PRIMARY KEY CLUSTERED (UserID, RecDate)
);
INSERT #TableB
(UserID, RecDate, Age)
VALUES
(123, '2007-08-12', NULL),
(123, '2010-11-02', 92),
(123, '2008-09-22', 90),
(124, '2007-09-22', 67),
(124, '2008-09-22', NULL),
(124, '2010-09-22', NULL),
(124, '2009-09-22', 68);
UPDATE ta
SET Age = New.Age
FROM #TableA AS ta
CROSS APPLY
(
SELECT TOP (1)
tb.Age
FROM #TableB AS tb
WHERE
tb.UserID = ta.UserID
AND tb.Age IS NOT NULL
ORDER BY
tb.RecDate DESC
) AS New
WHERE
ta.Age IS NULL;
SELECT *
FROM #TableA AS ta;
DROP TABLE #TableA, #TableB;
-
This has a comparable execution plan to Paul's submission (uses his setup data):
UPDATE a
SET Age = (SELECT MAX(Age) FROM #TableB b WHERE b.UserID = a.UserID)
FROM #TableA a
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (5/20/2012)
This has a comparable execution plan to Paul's submission (uses his setup data)Different semantics (see HowardW's two observations earlier).
-
SQL Kiwi (5/21/2012)
dwain.c (5/20/2012)
This has a comparable execution plan to Paul's submission (uses his setup data)Different semantics (see HowardW's two observations earlier).
Very cryptic, but I get your point that I didn't update only where necessary.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (5/21/2012)
SQL Kiwi (5/21/2012)
dwain.c (5/20/2012)
This has a comparable execution plan to Paul's submission (uses his setup data)Different semantics (see HowardW's two observations earlier).
Very cryptic, but I get your point that I didn't update only where necessary.
Sorry I didn't mean to be cryptic; the two queries have the two important logical differences HowardW mentioned.
Aside from updating when not needed (Howard's second point), any existing non-NULL ages would be overwritten with NULL if they don't have an age in Table 2. This may or may not be the required behaviour, but it is a semantic difference (and explains why your query has an outer join, whereas mine uses an inner join).
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply