November 9, 2012 at 11:32 pm
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 1
old number
Table 2
old number new number
November 10, 2012 at 2:09 am
simple follow this query :
select newno into #t3 from #t2,#t1 where #t2.oldNo =#t1.oldNo
here t1,t2 are yr table
oldno ,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.
November 10, 2012 at 10:11 am
maruthipuligandla (11/9/2012)
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 1
old number
Table 2
old number new number
Sorry I don't understand your requirement.
Could you please post DDL of tables with sample records and expected output.
November 12, 2012 at 3:08 am
Hi 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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
That said.... Have you looked at the "Update" syntax in BOL?
November 12, 2012 at 6:41 am
If I understand the requirements correctly, it would look something like this:
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;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2012 at 2:03 am
Hi,
The query logic is exactly what i'm looking out for, But when i ran the query the MERGE statement is throwing below error,
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply