-2

This are my SQL columns: ItemID, ItemName, Unit, Quantity, UnitCost, Total, Status

ItemID is my barcode number and primary key. Quantity is an integer and has an identity increment value of 1.

If you call the same sql query twice, it doesn't allow you to duplicate a primary key.

What I want: If the query was called twice, then increase the Quantity value by 1, and double the UnitCost value, to be placed in the Total section.

My idea was that if it was a duplicate key, then just update it.

ON DUPLICATE KEY UPDATE SET

but I'm getting errors all over the place.

string sql = @"INSERT INTO Grocery (ItemID, ItemName, Unit, UnitCost, Total, Status) VALUES ('2414', '2414', 'EACH', '1.5', '1.5', 'LP') ON DUPLICATE KEY UPDATE ItemName='changed'"; 
connection.Open();
        SqlCeCommand commandInsert = new SqlCeCommand(sql, connection);
                    commandInsert.ExecuteNonQuery();
Prix
  • 19,417
  • 15
  • 73
  • 132
Machina
  • 252
  • 2
  • 13
  • this question is duplicate please refer this http://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update – senthilkumar2185 Mar 01 '15 at 04:48

1 Answers1

1

Its not possible to update the values in a table by inserting it.

You have to,

First set the Quantity is normal integer column

Then check the given item already exists in the table.

  • If exists, then update the Quantity and Total columns

  • Else insert the item into the table

Hope this helps.

SelvaS
  • 2,105
  • 1
  • 22
  • 31
  • Or use the `INSERT ... ON DUPLICATE KEY UPDATE` or merge for sql-server – Prix Mar 01 '15 at 04:38
  • But why we need quantity as identity column? – SelvaS Mar 01 '15 at 04:42
  • Besides identity, On duplicate key doesn't work for me. Gives me an error. Does the query string have to be multi line formatted? – Machina Mar 01 '15 at 04:45
  • @Machina possible post your table schema here. – SelvaS Mar 01 '15 at 04:47
  • 1
    I believe On Duplicate Key Update is for MySql. It looks like you are using Sql Server Ce, which doesn't support the MERGE statement mentioned already. In this case you must use insert/update to handle it, and determine if the record exists first to determine your branching action. – Mark Fitzpatrick Mar 01 '15 at 04:57