February 24, 2014 at 10:14 pm
Hi All,
I have a user properties table
user_id(not null)
gen_userlevel(not null)
gen_supervisor(not null)
gen_approvelimit(float, not null)
gen_email(nvarchar(50),null)
gen_department(nvarchar(200),null)
gen_division(nvarchar(200),null)
gen_optional_1(nvarchar(200),null)
gen_optional_2(nvarchar(200),null)
vac_assist(int, null)
vac_startdate(datetime,null)
vac_enddate(datetime,null)
vac_enabled(bit,null)
company_id(int,not null)
Problem: user_id 4 & other's in some companys are missing their email address but it does exist in other companies. I would like to insert or update a users gen_email when it is missing from a company where is not missing.
current:
user_id 4
gen_userlevel 6
gen_supervisor 12
gen_approvelimit 1000
gen_email abcd@domain.com
.......
company_id 3
user_id 4
gen_userlevel 6
gen_supervisor 12
gen_approvelimit 1000
gen_email (blank)
.......
company_id 5
user_id 4
gen_userlevel 6
gen_supervisor 12
gen_approvelimit 1000
gen_email (blank)
.......
company_id 12
Desired:
user_id 4
gen_userlevel 6
gen_supervisor 12
gen_approvelimit 1000
gen_email abcd@domain.com
.......
company_id 3
user_id 4
gen_userlevel 6
gen_supervisor 12
gen_approvelimit 1000
gen_email abcd@domain.com
.......
company_id 5
user_id 4
gen_userlevel 6
gen_supervisor 12
gen_approvelimit 1000
gen_email abcd@domain.com
.......
company_id 12
Current TSQL
insert into userproperties_backup2(p2.user_id,gen_Email,company_id)
select distinct p2.user_id,p2.gen_Email, p2.company_id
from userproperties p2
inner join userproperties p1
on p1.User_id = p2.User_id and p1.company_id = p2.company_id
where exists (select distinct gen_Email
from userproperties p1
where gen_Email is not null or gen_Email != ' ')
You can see my attempt above, can anyone assist thanks.
February 25, 2014 at 2:03 am
This should help you on your way. Post back if you are unsure of how to convert it into an update.
SELECT
po.[user_id], po.gen_userlevel, po.gen_supervisor, po.gen_approvelimit, po.gen_email, po.company_id,
'#' '#', -- spacer column
x.[user_id], x.gen_userlevel, x.gen_supervisor, x.gen_approvelimit, x.gen_email, x.company_id
FROM userproperties po
CROSS APPLY (
SELECT TOP 1
p.[user_id],
p.gen_userlevel,
p.gen_supervisor,
p.gen_approvelimit,
p.gen_email,
p.company_id
FROM userproperties p
WHERE p.[user_id] = po.[user_id]
AND (p.gen_Email IS NOT NULL OR po.gen_Email <> ' ')
ORDER BY p.company_id
) x
WHERE po.gen_Email IS NULL OR po.gen_Email = ' '
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 25, 2014 at 7:36 pm
Thanks a lot chris, i have never heard of cross join before works.
I ended up with this
UPDATE p1
SET
p1.[gen_Email] = p2.gen_email
,p1.[gen_Supervisor] = p2.gen_Supervisor
,p1.[gen_Approvelimit] = p2.gen_Approvelimit
,p1.gen_department = p2.gen_department
,p1.gen_division = p2.gen_division
,p1.gen_optional_1 = p2.gen_optional_1
,p1.gen_optional_2 = p2.gen_optional_2
FROM [userproperties] p1
inner join
(SELECT
--po.[user_id], po.gen_userlevel, po.gen_supervisor, po.gen_approvelimit, po.gen_email, po.company_id,
--'#' '#', -- spacer column
x.[user_id], x.gen_userlevel, x.gen_supervisor, x.gen_approvelimit, x.gen_email, x.company_id, x.gen_department,x.gen_division, x.gen_optional_1, x.gen_optional_2
FROM userproperties po
CROSS APPLY (
SELECT TOP 1
p.[user_id],
p.gen_userlevel,
p.gen_supervisor,
p.gen_approvelimit,
p.gen_email,
p.gen_department,
p.gen_division,
p.gen_optional_1,
p.gen_optional_2,
p.company_id
FROM userproperties p
WHERE p.[user_id] = po.[user_id]
AND (p.gen_Email IS NOT NULL OR po.gen_Email <> ' ')
ORDER BY p.company_id
) x
WHERE po.gen_Email IS NULL OR po.gen_Email = ' ' ) p2
on p1.User_id =p2.User_id
February 25, 2014 at 9:26 pm
ringovski (2/25/2014)
Thanks a lot chris, i have never heard of cross join before works.
It's not a CROSS [font="Arial Black"]JOIN[/font]. That's something completely different than the CROSS [font="Arial Black"]APPLY [/font]that Chris used.
--Jeff Moden
February 26, 2014 at 2:02 am
Two excellent articles by Paul White covering APPLY are linked in my sig.
If you take a closer look at the SELECT query I wrote, it's designed to show the update target on the LHS and the update source on the RHS. It gives you an opportunity to validate that the update will work correctly - "Stare and Compare" is often sufficient. It's then a simple matter to convert the SELECT into an UPDATE. You've overcomplicated it somewhat - and made it less efficient too as SQL Server will read the target table one time too many. It should look like this
UPDATE po SET
gen_Email = x.gen_email,
gen_Supervisor = x.gen_Supervisor,
gen_Approvelimit = x.gen_Approvelimit,
gen_department = x.gen_department,
gen_division = x.gen_division,
gen_optional_1 = x.gen_optional_1,
gen_optional_2 = x.gen_optional_2
FROM userproperties po
CROSS APPLY (
SELECT TOP 1
p.[user_id],
p.gen_userlevel,
p.gen_supervisor,
p.gen_approvelimit,
p.gen_email,
p.gen_department,
p.gen_division,
p.gen_optional_1,
p.gen_optional_2,
p.company_id
FROM userproperties p
WHERE p.[user_id] = po.[user_id]
AND (p.gen_Email IS NOT NULL AND po.gen_Email <> ' ')
ORDER BY p.company_id
) x
WHERE po.gen_Email IS NULL OR po.gen_Email = ' '
which is, as you can see, a minimal change from the original SELECT.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply