locked
SQL QUERY RRS feed

  • Question

  • User-1940827647 posted

    table name is        "tblSales"

    Cid      Date               ItemId   Quantity  Price

    C1        2009-01-01     ABC     100       1000.00

    C2        2009-10-01    ABC      200        2000.00

    C4        2009-12-01    PQR      200       2000.00

    ----------------------------------------------------

    C4       2009-12-01     PQR       200      2000.00             i WANT TO UPDATE THIS 200 TO 100.. 

    ------------------------------------------------------

    C2       2009-01-04     XYZ       500       NULL

    C1       2009-01-05     XYZ       NULL     500.00

    how to do it? Please somebody help me..

    Thursday, January 7, 2016 6:40 PM

All replies

  • User753101303 posted

    Hi,

    So my understanding is that you have two identical rows and that you want to update one of them. You could do (I used a table variable just to easily test without having to create a table):

    declare @t table (id int)
    insert into @t values (1),(2),(2),(3)
    update t set id=10 from (select top 1 * from @t where id=2) t
    select * from @t

    In short the principle is just to select top 1 to ensure you are just selecting a single row matching the where clause and to update that row.

    You may want to still double check if you shouldn't introduce a pk for convenience if you really end up with having to identify multiple rows having exactly the same values.

    Edit: the output is

    1
    10
    2
    3

    Thursday, January 7, 2016 6:49 PM
  • User-1716253493 posted

    Please explain your question

    Do you mean?

    UPDATE tblsales set Quantity=100 WHERE cid='C4'

    Friday, January 8, 2016 12:58 AM