March 22, 2010 at 4:06 pm
I want to merge records from table B to table A.
From Table B I want to select the max values and if that value is higher than the value in table A I want to update that column in table A.
I have written some SQL, but somehow the record in table A is NOT updated even though the values of all columns in table B are higher!
Here's my SQL:
UPDATE prospects SET companyname=(select case when srcTbl.companyname is NULL then targetTbl.companyname when srcTbl.companyname > targetTbl.companyname then srcTbl.companyname else targetTbl.companyname end as test)
,freedate1=(select case when srcTbl.freedate1 is NULL then targetTbl.freedate1 when srcTbl.freedate1 > targetTbl.freedate1 then srcTbl.freedate1 else targetTbl.freedate1 end as test)
,freeint1=(select case when srcTbl.freeint1 is NULL then targetTbl.freeint1 when srcTbl.freeint1 > targetTbl.freeint1 then srcTbl.freeint1 else targetTbl.freeint1 end as test)
from prospects as targetTbl
join
(select companyname,max(freedate1) as freedate1,max(freeint1) as freeint1
from prospects_copy group by companyname)srcTbl
on srcTbl.companyname=targetTbl.companyname
What am I doinhg wrong?
March 22, 2010 at 5:27 pm
You don't need the subqueries in your SET clause. You've already JOINED those values to get the maximums. Try as shown below.
By the way, if you are joining on company name, how are you ever going to update the rows in the source table that have company name = NULL (or when the source company name is greater than the target company name). ??? I'm not sure that plan was well thought out.
UPDATE targetTbl
SET
---companyname=case
---when srcTbl.companyname is NULL then targetTbl.companyname
---when srcTbl.companyname > targetTbl.companyname then srcTbl.companyname
---else targetTbl.companyname end,
freedate1=case
when srcTbl.freedate1 is NULL then targetTbl.freedate1
when srcTbl.freedate1 > targetTbl.freedate1 then srcTbl.freedate1
else targetTbl.freedate1 end,
freeint1=case
when srcTbl.freeint1 is NULL then targetTbl.freeint1
when srcTbl.freeint1 > targetTbl.freeint1 then srcTbl.freeint1
else targetTbl.freeint1 end
from prospects as targetTbl
join
(select companyname,max(freedate1) as freedate1,max(freeint1) as freeint1
from prospects_copy
group by companyname) srcTbl on srcTbl.companyname=targetTbl.companyname
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 23, 2010 at 5:07 am
Not sure if I fully understood the requirements, but...
DECLARE @Source
TABLE (
companyname VARCHAR(10) NOT NULL,
freedate DATETIME NOT NULL,
freeint INTEGER NOT NULL
);
DECLARE @Target
TABLE (
companyname VARCHAR(10) NOT NULL,
freedate DATETIME NOT NULL,
freeint INTEGER NOT NULL
);
INSERT @Source
(companyname, freedate, freeint)
SELECT 'a1', '2001-07-01', 10 UNION ALL
SELECT 'b1', '2002-07-01', 20;
INSERT @Target
(companyname, freedate, freeint)
SELECT 'a1', '2001-01-31', 12 UNION ALL
SELECT 'a1', '2001-12-31', 11 UNION ALL
SELECT 'b1', '2002-01-31', 19 UNION ALL
SELECT 'b1', '2002-06-30', 21;
UPDATE S
SET freedate = DT.max_freedate,
freeint = DT.max_freeint
FROM @Source S
JOIN (
SELECT -- Maximum column value from either table
-- grouped by company name
companyname,
max_freedate = MAX(freedate),
max_freeint = MAX(freeint)
FROM (
-- Union source and target
SELECT companyname, freedate, freeint
FROM @Source S
UNION ALL
SELECT companyname, freedate, freeint
FROM @Target T
) X
GROUP BY
companyname
) DT
-- Update join condition
ON DT.companyname = S.companyname;
SELECT companyname,
freedate,
freeint
FROM @Source;
Output:
companyname freedate freeint
a1 2001-12-31 12
b1 2002-07-01 21
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 23, 2010 at 11:07 am
Both queries you provided dont seem to work 🙁
In table "prospects" the value of columns freedate1 and freeint1 are both NULL
In table "prospects_copy" the value of columns freedate1 is "2011-02-01 00:00:00.000" and freeint1 is "6"
After executing the queries, the values in prospects are still NULL...
@The Dixie Flatline:
Companyname can never be NULL in my case. I have a separate query that inserts new records if the company does not exist yet. What im trying here is purely to identity duplicate records and merging those into a fully enriched single record in the prospects table.
@paul-2 White NZ:
here's the query I executed:
UPDATE S
SET freedate1 = DT.max_freedate,
freeint1 = DT.max_freeint
FROM prospects_copy S
JOIN (
SELECT -- Maximum column value from either table
-- grouped by company name
companyname,
max_freedate = MAX(freedate1),
max_freeint = MAX(freeint1)
FROM (
-- Union source and target
SELECT companyname, freedate1, freeint1
FROM prospects_copy S
UNION ALL
SELECT companyname, freedate1, freeint1
FROM prospects T
) X
GROUP BY
companyname
) DT
-- Update join condition
ON DT.companyname = S.companyname;
March 23, 2010 at 11:15 am
petervdkerk (3/23/2010)
After executing the queries, the values in prospects are still NULL...
Not a massive surprise, since you are updating the prospects_copy table with my code.
I provided the code as a demonstration, not as a copy-and-paste solution.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 23, 2010 at 11:38 am
You're right, switched the 2 tables and now it does work 🙂
So, even maybe if you didnt fully mean to, it IS a copy paste solution 🙂
Thanks!
March 23, 2010 at 1:40 pm
Your original was a little confused with respect to the source and the target table. I assume you wanted the target table to be the table updated, and the source to be the table from which the updates come.
declare @prospects table (companyname varchar(50) primary key, freedate1 datetime, freeint1 int)
declare @prospects_copy table (companyname varchar(50) primary key, freedate1 datetime, freeint1 int)
insert into @prospects
select 'Company A', null, null union all
select 'Company B', '1/1/2008', 100 union all
select 'Company C', '1/1/2010', 200
insert into @prospects_copy
select 'Company A', '1/1/2009', 150 union all
select 'Company B', '1/1/2009', 150 union all
select 'Company C', '1/1/2010', 150
UPDATE targetTbl
SET
freedate1=case
when targetTbl.freedate1 is NULL then srcTbl.freedate1
when srcTbl.freedate1 > targetTbl.freedate1 then srcTbl.freedate1
else targetTbl.freedate1 end,
freeint1=case
when targetTbl.freeint1 is NULL then srcTbl.freeint1
when srcTbl.freeint1 > targetTbl.freeint1 then srcTbl.freeint1
else targetTbl.freeint1 end
from @prospects as targetTbl
join
(select companyname,max(freedate1) as freedate1,max(freeint1) as freeint1
from @prospects_copy
group by companyname) srcTbl on srcTbl.companyname=targetTbl.companyname
Select * from @prospects
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 23, 2010 at 10:14 pm
petervdkerk (3/23/2010)
You're right, switched the 2 tables and now it does work 🙂So, even maybe if you didnt fully mean to, it IS a copy paste solution 🙂 Thanks!
Glad it worked out for you 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply