Help in query for assigning defaul value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help in query for assigning defaul value

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-06-30 : 08:38:25

I have a userbranchmapping table which have userid, and branchid.
Now I have added one columdn for IsDefaultBranch
Currently i have data in such view as

Branch User IsDefaultBranch

1 7 0
4 7 0
5 7 NULL
1 1 0
4 1 1
5 1 0
2 2 1
3 3 NULL
1 3 0
5 8 NULL
What i want is to have a update query in which select all those user who doesn't have any defaultbranch value as True and let set True on the first record
like in above data i will need to set isdefault to true is userid 3, 7 and 8

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 08:49:07

update tbl
set IsDefaultBranch = 1
from tbl t1
where user in (select user from tbl group by user having max(coalesce(IsDefaultBranch,0)) = 0
and branch = (select min(branch) from tbl t2 where t1.user = t2.user)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-06-30 : 08:56:58

My actual database table is
select fkBranchID,fkUserID,IsDefaultBranch from UserBranchMapper

I have tried

update UserBranchMapper
set IsDefaultBranch = 1
from UserBranchMapper t1
where fkUserID in (select fkUserID from UserBranchMapper group by fkUserID having max(coalesce(IsDefaultBranch,0)) = 0
and fkBranchID = (select min(fkBranchID) from UserBranchMapper t2 where t1.fkUserID = t2.fkUserID))

But getting the error
Column 'UserBranchMapper.fkBranchID' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 09:01:52

Who designed your system (let me guess - an access developer)?

update UserBranchMapper
set IsDefaultBranch = 1
from UserBranchMapper t1
where fkUserID in (select fkUserID from tbl group by fkUserID having max(coalesce(IsDefaultBranch,0)) = 0
and fkBranchID = (select min(fkBranchID) from tbl t2 where t1.fkUserID = t2.fkUserID)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-06-30 : 12:02:01

Why about access developer?
it is not the exact table.just part and the default column is added recently.

above query

update UserBranchMapper
set IsDefaultBranch = 1
from UserBranchMapper t1
where fkUserID in (select fkUserID from UserBranchMapper group by fkUserID having max(coalesce(IsDefaultBranch,0)) = 0
and fkBranchID = (select min(fkBranchID) from UserBranchMapper t2 where t1.fkUserID = t2.fkUserID)
is throwing syntax error

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 12:18:44

What's the error?
There's a missing braket at the end of the in clause

update UserBranchMapper
set IsDefaultBranch = 1
from UserBranchMapper t1
where fkUserID in (select fkUserID from UserBranchMapper group by fkUserID having max(coalesce(IsDefaultBranch,0)) = 0)
and fkBranchID = (select min(fkBranchID) from UserBranchMapper t2 where t1.fkUserID = t2.fkUserID)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-07-01 : 01:25:51

Thanks Dear

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-01 : 06:18:29

Who's Dear?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources