update in single statement

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  November 27, 2009 at 3:55 am

  #137161

  Hi

  i have the following code. i want to do this in a single statement instead of inserting ifrst into temp table then updating from it. how can i achieve it

  select max(date) date, d.Id into #t1

  from cants d

  where source ='LAWS'

  group by d.Id

  update cants

  set first_priority = 1

  from #t1 t

  where cants.Id =t.Id

  and cants.date =t.date

  i want to set the priority to 1 for the records are having max(date) among ids and the source ='laws'

  thanks,

  regards

  anamika

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  November 27, 2009 at 4:16 am

  #1084845

  Maybe this?

  update cants

  set first_priority = 1

  from (select max(date) date, d.Id

  from cants d

  where source ='LAWS'

  group by d.Id) t

  where cants.Id =t.Id

  and cants.date =t.date

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537