SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


update command


update command

Author
Message
varshini
varshini
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 107
Table : Employee
EmployeeId Name MemoStatus
1 a N
2 b N
3 c Y
4 d N
5 e y
6 f N

Table MemoDetail :

EmpoyeeID iSSuedBy Date
3 Edwin 2009-01-05
3 Elsa 2009-02-10
5 Rocky 2009-03-03
5 bela 2009-02-01




i have to update Employee Memostatus to 'N' based on records not exist on Memodetail table.
Suppose employee has at least one memo record in memeoDetail table we should not update 'N' , it should be remain 'Y'
how to achive this using a update command ?
arun.sas
arun.sas
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3719 Visits: 3493
Hi,

try this

update a
set a.MemoStatus = (case when (select 1 from MemoDetail b where b.EmployeeId = a.EmployeeId )=1 then 'Y'else 'N' end)
from Employee a


Paul White
Paul White
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44113 Visits: 11367
arun.sas (11/7/2009)
Hi,

try this

update a
set a.MemoStatus = (case when (select 1 from MemoDetail b where b.EmployeeId = a.EmployeeId )=1 then 'Y'else 'N' end)
from Employee a


declare @Employee table (employee_id int primary key, name char(1) not null, memostatus char(1) null);

insert @Employee (employee_id, name) values (1, 'a');
insert @Employee (employee_id, name) values (2, 'b');
insert @Employee (employee_id, name) values (3, 'c');
insert @Employee (employee_id, name) values (4, 'd');
insert @Employee (employee_id, name) values (5, 'e');
insert @Employee (employee_id, name) values (6, 'f');

declare @MemoDetail table (employee_id int not null, issued_by varchar(10) not null, date datetime not null);

insert @MemoDetail (employee_id, issued_by, date) values (3, 'Edwin', '2009-01-05');
insert @MemoDetail (employee_id, issued_by, date) values (3, 'Elsa', '2009-02-10');
insert @MemoDetail (employee_id, issued_by, date) values (5, 'Rocky', '2009-03-03');
insert @MemoDetail (employee_id, issued_by, date) values (5, 'Bela', '2009-02-01');

update a
set a.MemoStatus = (case when (select 1 from @MemoDetail b where b.employee_id = a.employee_id )=1 then 'Y'else 'N' end)
from @Employee a

select * from @Employee


Msg 512, Level 16, State 1, Line 17
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.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44113 Visits: 11367
declare @Employee table (employee_id int primary key, name char(1) not null, memostatus char(1) null);

insert @Employee (employee_id, name) values (1, 'a');
insert @Employee (employee_id, name) values (2, 'b');
insert @Employee (employee_id, name) values (3, 'c');
insert @Employee (employee_id, name) values (4, 'd');
insert @Employee (employee_id, name) values (5, 'e');
insert @Employee (employee_id, name) values (6, 'f');

declare @MemoDetail table (employee_id int not null, issued_by varchar(10) not null, date datetime not null);

insert @MemoDetail (employee_id, issued_by, date) values (3, 'Edwin', '2009-01-05');
insert @MemoDetail (employee_id, issued_by, date) values (3, 'Elsa', '2009-02-10');
insert @MemoDetail (employee_id, issued_by, date) values (5, 'Rocky', '2009-03-03');
insert @MemoDetail (employee_id, issued_by, date) values (5, 'Bela', '2009-02-01');

update E
set memostatus =
case
when exists (select * from @MemoDetail M where M.employee_id = E.employee_id)
then 'Y'
else 'N'
end
from @Employee E;

select * from @Employee





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
arun.sas
arun.sas
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3719 Visits: 3493
[b]Paul White (11/7/2009Msg 512, Level 16, State 1, Line 17
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.


Thanks Paul,

It should be like
update a 
set a.MemoStatus = (case when (select distinct 1 from @MemoDetail b where b.employee_id = a.employee_id )=1 then 'Y'else 'N' end)
from @Employee a


Paul White
Paul White
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44113 Visits: 11367
I prefer the EXISTS. The plan seems more efficient, and the construct more natural.
TOP (1) would also work in place of the DISTINCT, but I'd still prefer the semi join.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Florian Reischl
Florian Reischl
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11021 Visits: 3934
Hi Guys!

@Paul: Thanks for test data! :-)

I always prefer the joined UPDATE:
UPDATE e SET
e.memostatus = CASE WHEN m.employee_id IS NULL THEN 'N' ELSE 'Y' END
FROM @Employee e
LEFT JOIN @MemoDetail m ON e.employee_id = m.employee_id



If there are many duplicates in memo-details table, I'd use a CTE:
; WITH m (employee_id) AS
(
SELECT DISTINCT
employee_id
FROM @MemoDetail
)
UPDATE e SET
e.memostatus = CASE WHEN m.employee_id IS NULL THEN 'N' ELSE 'Y' END
FROM @Employee e
LEFT JOIN m ON e.employee_id = m.employee_id



Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Paul White
Paul White
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44113 Visits: 11367
Florian Reischl (11/7/2009)
@Paul: Thanks for test data! :-)

You're very welcome. I'm hoping that if I do it enough, future posters will get the hint :-)

I know not everything in life is about performance, but if you compare the query plans for your preferred methods to the EXISTS version, I hope you will see why I prefer it.

The EXISTS does not need a Stream Aggregate (present in both of your examples) and uses a left semi join instead of a left outer join. The obvious difference there is that the semi join stops processing as soon as it finds a match; the left outer join processes all matches.

Query plans:

1. EXISTS
2. LEFT JOIN
3. CTE



Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
Florian.png (193 views, 67.00 KB)
Florian Reischl
Florian Reischl
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11021 Visits: 3934
Paul White (11/7/2009)
The EXISTS does not need a Stream Aggregate (present in both of your examples) and uses a left semi join instead of a left outer join. The obvious difference there is that the semi join stops processing as soon as it finds a match; the left outer join processes all matches.

Thanks for your explanation!

I know, the joined UPDATE is slightly less performing in this case. The main reason why I'm using it is, I've seen several inline sub-queries which ended up in a row based execution. Since I don't know the SQL Server internals like you do, the joined syntax is more secure for newbies like me :-P.

Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Paul White
Paul White
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44113 Visits: 11367
Newbies??? Flo - in the words of a very popular drink over here..."yeah, right!"

http://www.tui.co.nz/





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
Newbie.jpg (181 views, 28.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search