InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 14:39:21 GMT20http://www.sqlservercentral.com/Forums/Topic1383334-392-1.aspxHi,The query logic is exactly what i'm looking out for, But when i ran the query the MERGE statement is throwing below error,[b]The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.[/b]Sat, 17 Nov 2012 02:03:11 GMTmaruthipuligandlahttp://www.sqlservercentral.com/Forums/Topic1383334-392-1.aspxIf I understand the requirements correctly, it would look something like this:[code="sql"]IF OBJECT_ID(N'tempdb..#T1') IS NOT NULL DROP TABLE #T1; IF OBJECT_ID(N'tempdb..#T2') IS NOT NULL DROP TABLE #T2; CREATE TABLE #T1 ([Number] VARCHAR(10));CREATE TABLE #T2 ([OldNumber] VARCHAR(10), [NewNumber] VARCHAR(10));INSERT INTO #T1 (Number)VALUES ('10'), ('20'), ('30'), ('40');INSERT INTO #T2 (OldNumber, NewNumber)VALUES ('10', '15'), ('20', 'MISSING'), ('30', NULL);SELECT *FROM #T1;MERGE INTO #T1 AS Tgt USING (SELECT #T2.OldNumber, #T2.NewNumber FROM #T2) AS Src ON Tgt.Number = Src.OldNumber WHEN MATCHED AND Src.NewNumber IS NOT NULL AND Src.NewNumber != 'MISSING' THEN UPDATE SET Number = Src.NewNumber; SELECT *FROM #T1;[/code]Does that help?Mon, 12 Nov 2012 06:41:27 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1383334-392-1.aspxHi maruthipuligandla,The requirement is to make your problem as clear as possible so that we can help you better.Take the time to script a table and test data. That way we can just copy and paste into a new query window and start figuring out a solution to your problem.Read Jeff Moden's article:[url] http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]That said.... Have you looked at the "Update" syntax in BOL?Mon, 12 Nov 2012 03:08:50 GMTDennis Posthttp://www.sqlservercentral.com/Forums/Topic1383334-392-1.aspx[quote][b]maruthipuligandla (11/9/2012)[/b][hr]Hi,I'm having 2 tables, One table holds some numbers of type varchar and the other table holds the same numbers present in 1st table along with one extra column "NEW NUMBERS". Now i need to update the table 1 with new numbers from table 2 if old number in table 1 matches with table 2 old number..Also i need to write a condition like, Even though a match is found in table 2, if corresponding new number is empty or string like "MISSING", i should not then update.Please help me out ..Table 1old numberTable 2old number new number[/quote]Sorry I don't understand your requirement.Could you please post DDL of tables with sample records and expected output.Sat, 10 Nov 2012 10:11:01 GMTrhythm.varshneyhttp://www.sqlservercentral.com/Forums/Topic1383334-392-1.aspx[quote] I'm having 2 tables, One table holds some numbers of type VARCHAR and the other table holds the same numbers present in 1st table along with one extra column "NEW NUMBERS". [/quote]Why would you use strings for numbers in a programming language that has numeric data types? Why do you still refuse to post DDL after we have been telling you this is minimal polite behavior in an SQL Forum? Why does this narrative describe redundancy in design? You are doing everything completely wrong. Sat, 10 Nov 2012 09:20:01 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1383334-392-1.aspxsimple follow this query :select newno into #t3 from #t2,#t1 where #t2.oldNo =#t1.oldNohere t1,t2 are yr tableoldno ,newno r column names of t2 t3 is new table created as a result of your query.here you can write any condition as just you write in any other query.Sat, 10 Nov 2012 02:09:24 GMTKcVhttp://www.sqlservercentral.com/Forums/Topic1383334-392-1.aspxHi,I'm having 2 tables, One table holds some numbers of type varchar and the other table holds the same numbers present in 1st table along with one extra column "NEW NUMBERS". Now i need to update the table 1 with new numbers from table 2 if old number in table 1 matches with table 2 old number..Also i need to write a condition like, Even though a match is found in table 2, if corresponding new number is empty or string like "MISSING", i should not then update.Please help me out ..Table 1old numberTable 2old number new numberFri, 09 Nov 2012 23:32:47 GMTmaruthipuligandla