|
I have a stored procedure that is updating, but causing an insert error. Never seen that before.
Update MyCoolTable
Set MyValue=@passedInValue, SecondaryIdentifier=@secondID
WHERE PrimaryID = @primaryID
There is an index on (SecondaryID,PrimaryID) that is being violated and I'm not sure why. :
Quote: Cannot insert duplicate key in object 'dbo.MyCoolTable'. The duplicate key value is (secondaryIDValue, primaryIDValue).
Why is it trying to insert on an update?
--edit--
Nevermind, I figured out what was happening (though not from the error message). It can't update the secondaryID because there can be more than one, which is what is causing the problem. I guess when they wrote it that could never ever happen.
If it moves, compile it
modified 17-Sep-12 13:41pm.
|
|
|
|
|
loctrice wrote: I guess when they wrote it that could never ever happen
You might wanna post this a tip/trick. Maybe in this way it'll get searchable more easily by others having the same problem (and hopefully avoid another This is failing dunno why plz provide codez Q&A question)
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Andrei Straut wrote: avoid another This is failing dunno why plz provide codez
was there something wrong with my question? I know it was a dumb question but my brain was just slow today. I guess I should/could have waited longer to post.
If it moves, compile it
|
|
|
|
|
Oh, sorry for not making it clearer. I wasn't referring to your question (and I did upvote it), that was posted just fine from my point of view. I was referring to someone else having this problem in the future and posting in the manner I described.
Didn't think you'd think I'd think that of your question (pun intended).
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Andrei Straut wrote: Oh, sorry for not making it clearer.
my fault. glad we got it cleared up 
If it moves, compile it
|
|
|
|
|
how to add pictures and videos in mySQL???plz help me
|
|
|
|
|
It's very simple.
You try a Google search[^] first, then after you've done that and got some sort of idea of what you need to do you start doing it, and then when and if you get stuck, you come back, you explain your problem clearly and with detail (while providing the smallest possible amount of relevant code), and someone will probably give you an idea. This is how it works.
Until then, I am downvoting your question
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Snarky, bitchy and oh so polite - I love have another 5
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
10x. I hope I'm making my point across while repeating myself over and over again. If only they'd listen...
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Andrei Straut wrote: If only they'd listen.
You can try shouting a bit louder, but that just gets you a sore throat. 
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Chris doesn't like shouting in the Q&A / programming forums 
I do feel a really strong urge to do it however
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
You should actually have it in your sig.
|
|
|
|
|
If only you knew how many times a day I think about it...
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
I have table A with col1, col2 & table B with col3, col4.
I would like to write a query like
select a.* from A join B on A.col1=B.col3 and A.col2 like col4 + '%'
Is it possible in SQL 2005?
is there a concat function in SQL 2008 or 2005. I have seen it in 2012.
Pls help me.
Thanks in advance
|
|
|
|
|
Anything wrong with google today?
Have a look here[^].
String concatenation has been around as long as SQL has. But different dialects have different syntax.
Oracle uses '||', Sql Server uses '+', most implementations can use 'concat(string1, string2)' but not SQL Server AFAIK. Could be wrong about that though.
|
|
|
|
|
Jörgen Andersson wrote: Anything wrong with google today?
Same impression here, I'm starting to believe I'm the only person with Google access today 
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Hi,
In MySQL, how can I format a number like this
1000000001
where the first 1 is the weekday and the last one is my LAST_INSERT_ID and I am adding just zeroos to make it always 10 digits?
|
|
|
|
|
How about this:
SELECT (DAYOFWEEK(NOW()) * 1E9) + @@LAST_INSERT_ID
|
|
|
|
|
Hi,
I want to update the column TID with the top most value of TID as long as MID is the same number.
I want to do this (table after update)
PID SID TID MID RID
3 0 0 4 1
3 NULL 0 4 2
3 NULL 0 4 3
3 NULL 0 4 4
3 NULL 0 4 5
3 NULL 0 4 6
3 NULL 0 4 7
3 4 4 8 1
3 NULL 4 8 2
3 NULL 4 8 3
3 NULL 4 8 4
Below is the actual table before update.
PID SID TID MID RID
3 0 0 4 1
3 NULL NULL 4 2
3 NULL NULL 4 3
3 NULL NULL 4 4
3 NULL NULL 4 5
3 NULL NULL 4 6
3 NULL NULL 4 7
3 4 4 8 1
3 NULL NULL 8 2
3 NULL NULL 8 3
3 NULL NULL 8 4
Any suggestion/solutions deeply appreciated.
Thanks
|
|
|
|
|
Assuming your DB is SQL Server, you can try this (just out of mind, not tried though)
UPDATE MyTable A SET A.TID = (SELECT MAX(B.TID) FROM MyTable B WHERE A.MID = B.MID)
|
|
|
|
|
Thanks for your help.
I have solved it by creating a view of all 'Not NULL' (top most) rows . Then created a stored proc to loop through the view and update the TID. A long procedure though but it did work.
Cheers !
|
|
|
|
|
I think you want to do something like this ...
update table A
set tid =
(select max(tid)
from TableA
Group By mid)
Check these for references:
http://msdn.microsoft.com/en-us/library/ms177523.aspx
http://data.bangtech.com/sql/sql_update_with_correlated_subquery.htm
|
|
|
|
|
Hi thanks for your solution and links,
But your query returned an error.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
|
|
|
|
|
That's because he forgot a small but important condition.
Try this:
update table A as a1
set tid =
(select max(tid)
from TableA as A2
Where A1.mid = A2.mid
Group By mid)
|
|
|
|
|
Thanks for the refinement to my SQL. Sometimes when you don't have the actual data to test with, you forget some stuff.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
