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


update command


update command

Author
Message
varshini
varshini
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 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
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6757 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 Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85284 Visits: 11402
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 Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85284 Visits: 11402
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
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6757 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 Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85284 Visits: 11402
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
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21985 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 Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85284 Visits: 11402
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 (206 views, 67.00 KB)
Florian Reischl
Florian Reischl
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21985 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 Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85284 Visits: 11402
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 (192 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