SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update one table rows with another table if match found


Update one table rows with another table if match found

Author
Message
User7766
User7766
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 226
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
KcV
KcV
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 222
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.
rhythm.varshney
rhythm.varshney
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 195
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.
Dennis Post
Dennis Post
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 547
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/

That said.... Have you looked at the "Update" syntax in BOL?



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24635 Visits: 9730
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
User7766
User7766
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 226
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search