SQL Update statement

[JellybeanFB]

"UPDATE P SET Stock = T.Stock FROM prices P INNER JOIN #prices " +
                "T ON P.Avg = T.Avg AND P.Colour = T.Colour;" +
                "  " +
                "INSERT INTO prices(Avg, Colour, Stock) SELECT T.Avg, T.Colour, T.Stock FROM #prices T " +
                "WHERE NOT EXISTS (SELECT 1 FROM prices P WHERE P.Avg = T.Avg AND P.Colour = T.Colour);";

I'm using c# to import excel data to sql server, if the record exists update else insert new record with Stock field as the primary key. My code inserts new records without any problems, If I reupload without any changes in the excel file the database also doesn't change, BUT if I change the Avg or Colour field in the excel then a new record is inserted and not updated, can someone please help?

From the excel file I insert data into a temp table (#prices) which inserts to sql server. Please note I'm using sql server 2005.

[Momerath]

WHERE NOT EXISTS (SELECT 1 FROM prices P WHERE P.Avg = T.Avg AND P.Colour = T.Colour)

This part of the insert statement says if the average or the color aren't the same, insert a record. In your comments you say "if I change the average or color then a new record is inserted", which is exactly what you told it to do.

[JellybeanFB]

Thanks for the advice, will give it a shot.

[JellybeanFB]

I did what you suggested and removed the following line;

WHERE NOT EXISTS (SELECT 1 FROM prices P WHERE P.Avg = T.Avg AND P.Colour = T.Colour)

but now all the records are insterted, regardless if it exist or not. i need to update if exist and insert if not exist,

I tried;

WHERE EXISTS (SELECT 1 FROM prices P WHERE P.Avg = T.Avg AND P.Colour = T.Colour)

but then no new records are inserted.

[taskman]

Just to clarify, you say:

BUT if I change the Avg or Colour field in the excel then a new record is inserted and not updated, can someone please help?

So your problem is with this statement?

"UPDATE P SET Stock = T.Stock FROM prices P INNER JOIN #prices " + "T ON P.Avg = T.Avg AND P.Colour = T.Colour;"

Are you saying that, when you test by changing a non-key value in the source Excel table, rather than updating the correct record in the SQL target table, it is instead adding a new record?

[JellybeanFB]

Taskman,

Yes, you are understanding me correctly. The record needs to be updated and not inserted.

[taskman]

I can't see the rest of your code, of course, but are you placing two commands (UPDATE and INSERT) into the same command. I assume not, but the way you give it to us they seem to be concatenated together. If so, consider using IF structures in your C# code to test for existence and then either UPDATE or INSERT. That way you can break it down to watch step by step in the debugger to better see where the problem is.