January 17, 2011 at 9:32 am
I've got 2 tables with following sample data
Table A
id puser
1 joe
2 burt
3 jack
4 NULL
Table B
id primaryuser stime
1 joe 1/17/2011
2 burt 1/17/2011
3 Jack 1/17/2011
4 John 1/17/2011
I've got query that use data from TableA/B, using as link "id",
qryData
id puser last_seen_date
1 joe 1/17/2011
2 burt 1/17/2011
3 jack 1/17/2011
4 NULL 1/17/2011
What I like to have is, for the query when it returns "NULL" for puser I would like it to use the primaryuser from TableB?
Can this be done 1 qry/storedprocedure, or does it require multiple queries to get this result. the qry data contains
alot more data but just wanted to present it in simple way.
Thx in advance.
January 17, 2011 at 9:42 am
Something like this should work:
declare @TableA table (id int, puser varchar(5))
declare @TableB table (id int, primaryuser varchar(5), stime datetime)
insert into @TableA
select 1, 'joe' union all
select 2, 'burt' union all
select 3, 'jack' union all
select 4, null
insert into @TableB
select 1, 'joe', '1/17/2011' union all
select 2, 'burt', '1/17/2011' union all
select 3, 'Jack', '1/17/2011' union all
select 4, 'John', '1/17/2011'
select
id = a.id,
puser = case when a.puser is null then b.primaryuser else a.puser end,
last_seen_date = b.stime
from @TableA a
left join @TableB b
on a.id = b.id
- Jeff
January 17, 2011 at 11:20 am
IsNull() is more succinct than the CASE statement.
IsNull(a.PUser, b.PrimaryUser) AS PUser
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2011 at 4:04 am
All,
thx for the info, now follow-up question, how would I do this via Update statement
I've tried following
UPDATE [invdb].[dbo].[puser]
SET [username] = (SELECT puser = case when a.username = 'NULL' then b.loggedonuser else a.username end from puser a
inner join wks_info_test b on a.machine_id = b.machine_id
where a.machine_id = b.machine_id)
If I run this I get following error message
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I've currently got stored procedure which runs at night and populates the puser table with info.
I would like to add the option to update any entry with "NULL" and any invalid entries
January 18, 2011 at 4:52 am
considersing the same sample what pdanke has explanined.. we can use below query to update the data in TableA
UPDATE a
SET a.puser = COALESCE(a.puser, b.primaryuser)
from @TableA a
join @TableB b on a.id = b.id
Abhijit - http://abhijitmore.wordpress.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply