June 7, 2012 at 12:06 pm
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);"
Can someone please help with my code, the code is suppose to insert if not exist else update. My code inserts correctly, but does not update????
June 7, 2012 at 12:10 pm
Hi and welcome to SSC! Your post does not have anywhere near enough information for anybody to help. From your description it sounds like you should look at MERGE. http://msdn.microsoft.com/en-us/library/bb510625.aspx
If you need help with the actual code please read the first link in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2012 at 12:32 pm
What happens when you run this:
SELECT *
FROM prices P
INNER JOIN #prices T
ON P.Avg = T.Avg
AND P.Colour = T.Colour;
Also, what's with all of your concatenation? Is that also in the code (the +'s and "s)?
Jared
CE - Microsoft
June 7, 2012 at 12:36 pm
Other than all those double quotes (which I removed), I really don't see anything wrong with the code below:
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
);
June 7, 2012 at 12:38 pm
Also, what's with all of your concatenation? Is that also in the code (the +'s and "s)?
Looking at this again I suspect the OP is doing pass through queries from c#. That would certainly explain the oddball concatenation stuff going on. Just an fyi to the OP this type of query is considered bad practice. You should move your queries to a stored procedure. Adding a data layer would be even better but stored procs at least.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2012 at 12:42 pm
If it is simply C#, then the problem is that the UPDATE does not have a double quote in front of it. Should be this:
"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);"
Jared
CE - Microsoft
June 7, 2012 at 7:54 pm
Lynn Pettis (6/7/2012)
Other than all those double quotes (which I removed), I really don't see anything wrong with the code below:
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
);
Lynn - Your code looks to me like a MERGE in disguise. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 7, 2012 at 11:51 pm
Hi Guys,
The "" is becuase I use the sql statement in c#
June 8, 2012 at 5:22 am
Meccer (6/7/2012)
Hi Guys,The "" is becuase I use the sql statement in c#
Ok, well your SQL is fine. Add a " before the UPDATE.
Jared
CE - Microsoft
June 8, 2012 at 6:10 am
IF EXISTS(SELECT 1 FROM prices P WHERE P.Avg = T.Avg AND P.Colour = T.Colour)
BEGIN
UPDATE P
SET Stock = T.Stock
FROM prices P INNER JOIN #prices T ON P.Avg = T.Avg AND P.Colour = T.Colour;
END
ELSE
BEGIN
INSERT INTO prices(Avg, Colour, Stock) SELECT T.Avg, T.Colour, T.Stock FROM #prices T
END
Go
June 8, 2012 at 7:33 am
Meccer (6/7/2012)
Hi Guys,The "" is becuase I use the sql statement in c#
Well as I said previously you should consider moving your data to a stored procedure and the calling that from c#. Keeps things nice and tidy. It also means you have to parameterize your code but that gives you the advantage of preventing sql injection too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply