update command

• varshini

  Right there with Babe

  Points: 725

  More actions

  November 6, 2009 at 11:48 pm

  #136785

  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

  SSChampion

  Points: 11831

  More actions

  November 7, 2009 at 12:08 am

  #1076258

  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

  SSC Guru

  Points: 150467

  More actions

  November 7, 2009 at 1:07 am

  #1076267

  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

  [font="Courier New"]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.

  [/font][font="Courier New"]The statement has been terminated.[/font]

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Paul White

  SSC Guru

  Points: 150467

  More actions

  November 7, 2009 at 1:11 am

  #1076268

  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
  SQLkiwi blog
  @SQL_Kiwi

• arun.sas

  SSChampion

  Points: 11831

  More actions

  November 7, 2009 at 1:12 am

  #1076269

  Paul White (11/7/2009[font="Courier New"]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.

  [/font][font="Courier New"]The statement has been terminated.[/font]

  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

  SSC Guru

  Points: 150467

  More actions

  November 7, 2009 at 1:45 am

  #1076271

  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
  SQLkiwi blog
  @SQL_Kiwi

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  November 7, 2009 at 2:46 am

  #1076280

  Hi Guys!

  @paul-2: 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

• Paul White

  SSC Guru

  Points: 150467

  More actions

  November 7, 2009 at 2:57 am

  #1076281

  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
  SQLkiwi blog
  @SQL_Kiwi

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  November 7, 2009 at 3:03 am

  #1076283

  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

• Paul White

  SSC Guru

  Points: 150467

  More actions

  November 7, 2009 at 3:21 am

  #1076290

  Newbies??? Flo - in the words of a very popular drink over here..."yeah, right!"

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

  

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  November 7, 2009 at 3:30 am

  #1076291

  Well, probably I'm no newbie :cool:. But there are still situations I completely don't understand SQL Server's intentions... (Just had a really strange behavior last week which appeared completely stupid to me.)

  As long as I don't understand those things I prefer the secure in some situations.

  Greets

  Flo

• Paul White

  SSC Guru

  Points: 150467

  More actions

  November 7, 2009 at 3:43 am

  #1076293

  Florian Reischl (11/7/2009)

  ---

  Just had a really strange behavior last week which appeared completely stupid to me.

  Feel free to share! PM or The Thread...

  Florian Reischl (11/7/2009)

  ---

  As long as I don't understand those things I prefer the secure familiar in some situations.

  Fixed that for you 🙂

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  November 7, 2009 at 3:59 am

  #1076297

  Paul White (11/7/2009)

  ---

  Florian Reischl (11/7/2009)

  ---

  Just had a really strange behavior last week which appeared completely stupid to me.

  Feel free to share! PM or The Thread...

  Good idea. I'll try to setup a test environment and start a thread.

  Thanks

  Flo

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  November 7, 2009 at 5:41 am

  #1076305

  Paul White (11/7/2009)

  ---

  Florian Reischl (11/7/2009)

  ---

  Just had a really strange behavior last week which appeared completely stupid to me.

  Feel free to share! PM or The Thread...

  Feel free to join:

  http://www.sqlservercentral.com/Forums/Topic815391-338-1.aspx

  🙂

• Paul White

  SSC Guru

  Points: 150467

  More actions

  November 7, 2009 at 7:11 am

  #1076325

  Nice problem there Flo, and good to get it solved so quickly.

  I hope other posters imitate your set-up scripts!

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi