Populate column where max - SQL Server Forums

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)

Populate column where max

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-25 : 15:25:15

Hello All,

I have to write a update statement where the proce need to be populated to zero if the following criteria matches.

table :


Location  customer   TaskID    unitprice   ActTaskID   CompID
A001      C1001      3130      500         3130        S001
A001      C1001      3130      500         3030        PS02
A001      C1001      3130      500         3100        XS23

B001      C1001      34269     40          28460       AS22
C001      C1001      33651     50          33651       BB01
B001      C1001      34269     40          22344       D10A
B001      C1001      34269     40          22340       R900

Here I have to udpate the unitprice column. For each location + customer + TaskID, make unit priceprice zero for all the records except the first one where max(ActTaskID) = TaskID group by location,customer,TaskID
      
The results should look like this -
Location  customer   TaskID    unitprice   ActTaskID   CompID
A001      C1001      3130      500         3130        S001
A001      C1001      3130      0           3030        PS02
A001      C1001      3130      0           3100        XS23

B001      C1001      34269     40          28460       AS22
C001      C1001      33651     50          33651       BB01
B001      C1001      34269     0           22344       D10A
B001      C1001      34269     0           22340       R900

Any suggestions would be very helpful

Thanks,
-P

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-25 : 15:37:04

try this:


update t set 
       t.unitPrice = 0
from   yourTable t
left outer join (
       select location, customer, TaskID, max(ActTaskID) as masActTaskID
       from yourTable
       group by location, customer, TaskID
       ) d
              on d.location = t.location
              and d.customer = t.customer
              and d.taskid = t.maxActTaskID

where  d.location is null

Be One with the Optimizer
TG

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-25 : 15:49:47

Thanks TG.

Your suggestion works but if there are same ActTaskID then I want only first row to be updated which dosn't happen in this case.

For example -


Location  customer   TaskID    unitprice   ActTaskID   CompID
A001      C1001      3130      500         3130        S001
A001      C1001      3130      0           3030        PS02
A001      C1001      3130      500         3130        XS23

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-25 : 15:56:30

which row is "first"? And what makes it first?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-25 : 15:58:12

perhaps add min or max CompID in the derived table and JOIN by that as well.

Be One with the Optimizer
TG

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-25 : 16:05:17

No it is not by compid as there are a few more other column I have to include.

I would say where the first match found keep the unitprice as is and rest of the rows make it zero.
and d.maxActtaskid = t.ActTaskID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-25 : 16:42:07

To Update just one row then you need to know what column combination makes the row unique. It is not possible to update just one of two rows where all values are equal.

>>there are a few more other column I have to include
So include all columns necessary to guarantee uniqueness

Be One with the Optimizer
TG

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-07-25 : 16:45:38

I think you are correct. I will include all the columns that make each row unique. But can you please help me to write the same in SQL query?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-25 : 17:03:51

Untested. But perhaps this is enough for you to go on:


update t set 
       t.unitPrice = 0
from   yourTable t
cross apply (
       select top 1 ActTaskID, [other cols that will guarantee uniqueness]
       from yourTable d
       where d.location = t.location
       and d.customer = t.customer
       and d.taskid = t.taskid
       order by ActTaskID desc
       ) ca
where   ca.ActTaskID = t.ActTaskID
and    ca.[CORRELATE each of the other cols] = t.[CORRELATE each of the other cols]

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources