Hi folks,   update sql code!  (The company pays once per type for employee)
before    employee    spendingType  amount     company_paid
                peter             subway           2                null 
                peter             subway           2                null 
                peter             train             10                 null 
after      employee    spendingType  amount     company_paid
                peter            subway            2                2
                peter            subway            2               null
                peter               train            10              10
Thanks.  Peter

Peter wrote:
How do you decide what the Company Pays?  Which row do you want to
update? What happens if there are 5 rows with varying amounts?

A question like this is useless as your requirements are not clear and
you haven't even provided sample data.

http://www.aspfaq.com/etiquette.asp?id=5006

Peter wrote:
What is the key of this table? Apparently you don't have one so you'd
better fix that first! Please post DDL with keys and constraints so
that we don't have to guess.

You can't directly update this table with a simple UPDATE statement as it is
because any where clause in the update statement that updates the first row
in your data will also update the second.  You could do it with a cursor or
you could do it by adding an identity column column to your table, doing the
update, and dropping the identity column.  But, of course, the correct way
to do this would be to begin by redesigning your table to a valid
normalizied design.  However, assuming you don't care which row gets updated
for any given combination of employee and spendingType, the following would
work:

Alter Table MyTable Add MyIdentity int identity
go
Update Mytable Set company_paid = amount
Where MyIdentity In
  (Select Min(MyIdentity) From MyTable
  Group By employee, spendingType)
go
Alter Table MyTable Drop Column MyIdentity
go


Tom

Show quoteHide quote

Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string
parse out set of numbers from a column