Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update one table rows with another table if match found Expand / Collapse
Author
Message
Posted Friday, November 9, 2012 11:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 28, 2014 10:24 PM
Points: 37, Visits: 150
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
Post #1383334
Posted Saturday, November 10, 2012 2:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:02 AM
Points: 65, Visits: 204
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.
Post #1383347
Posted Saturday, November 10, 2012 9:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 1,945, Visits: 3,025
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".


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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1383376
Posted Saturday, November 10, 2012 10:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, 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.
Post #1383380
Posted Monday, November 12, 2012 3:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 175, 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
Post #1383574
Posted Monday, November 12, 2012 6:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1383669
Posted Saturday, November 17, 2012 2:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 28, 2014 10:24 PM
Points: 37, Visits: 150
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.



Post #1385918
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse