Last post Jul 21, 2016 01:56 PM by Chris Zhao
Jul 20, 2016 10:57 AM|progy85|LINK
My code is like that.
INSERT INTO tbl_Suggestions(MatchId,OutCome,OutComeId,OddsType,[Value],SpecialBetValue,CreatedDate,TS.IsUpdated)
SELECT C.Cou.value('(MatchId)','bigint') As MatchId,C.Cou.value('(OutCome)','NVARCHAR(50)') As OutCome,
C.Cou.value('(OutComeId)','NVARCHAR(50)') As OutComeId,C.Cou.value('(OddsType)','int') As OddsType,
C.Cou.value('(Value)','NVARCHAR(50)') As OddValue,C.Cou.value('(SpecialBetValue)','NVARCHAR(50)') As SpecialBetValue,GETDATE(),0
FROM @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou)
LEFT JOIN tbl_Suggestions TS ON TS.MatchId = C.Cou.value('(MatchId)','bigint') AND
TS.OutComeId = C.Cou.value('(OutComeId)','NVARCHAR(50)') AND
TS.OddsType = C.Cou.value('(OddsType)','int')
WHERE TS.MatchId IS NULL AND TS.OutComeId IS NULL AND TS.OddsType IS NULL
SET TS.IsUpdated= 1
FROM tbl_Suggestions TS
WHERE MatchId= (Select MatchId inserted) AND OutComeId=(Select MatchId inserted)AND OddsType=(Select OddsType inserted)
When insert new rows in db I want to find old rows with the same matchid, outcomeid,oddstype and old rows i want update with isupdated=1
My above code not working.
Jul 20, 2016 11:49 AM|PatriceSc|LINK
Unclear. Which error message do you have? The syntax seems entirely wrong (or which db do you use ?) so my understanding is that you shown "(Select MatchId inserted)" to tell you don't know what to do here?
Not sure what is the "IsUpdated" column for but couldn't you set it to 1 when you insert those new rows so that you don't have to then to do another select to figure out which rows were just inserted (assuming I understood your issue). I assume you can
set this column to 0 before the insert.
Once I even used a guid column to have so to speak a "batch id" so that I could keep working on the rows I just inserted to do further operations (and maybe having other users working an another "batch id" at the same time). And so even later I could track which
rows were inserted as part of the same batch.
Jul 21, 2016 01:56 PM|Chris Zhao|LINK
Based on your description, consider using
MERGE to perform INSERT and UPDATE operations on a table in a single statement. A common scenario is updating one or more columns in a table if a matching row exists, or inserting the data as a new row if a matching row does not exist. This is usually done
by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. With the MERGE statement, you can perform both tasks in a single statement.