Update field in one table by looking it in second table.

Well there just one problem. the second table may have multiple entries. so consider table A

table A:

ROLLNUMBER   NAME
111                     na
111                     na

TABLE B:

ROLLNUMBER  ALIAS NAME
111                    GM     GAURAV
111                    MAN   GAURAV
111                    GAU   GAURAV


NOW I NEED TO UPDATE NAME IN TABLE A LOOKING IT UP IN TABLE B. INNER JOIN WILL GIVE ME 6 ENTRIES BUT I NEED JUST THE NAME SO AFTER UPDATE I SHOULD HAVE JUST 2 ROWS IN TABLE A.

THANKS



LVL 5
mandeliaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mandeliaAuthor Commented:
Dont worry the names are not not different and even if they are something like  First(name)  will solve my purpose
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
syntax depends on the database product/version you use,
please clarify

note: please do not post with ALL CAPS ON...
that's usually considered shouting...
0
mandeliaAuthor Commented:
right now MS Access.
 
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

mandeliaAuthor Commented:
oh ok that was not intentional
 
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this
UPDATE TABLEA
  SET NAME = DLOOKUP("NAME", "TABLEB", "ROLLNUMBER = " & ROLLNUMBER)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mandeliaAuthor Commented:
thanks for the frist part how do in do this in MS SQL  server 2005.
0
mandeliaAuthor Commented:
moreover is there a query that can do the same on multiple servers
0
mandeliaAuthor Commented:
i mean database independent
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in sql, this would be:
UPDATE a
  SET Name = b.Name
FROM TABLEA a
 JOIN TableB b
  on B.RollNumber = a.RollNumber

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in oracle:
UPDATE a
  SET Name = (SELECT b.Name FROM TableB b WHERE B.RollNumber = a.RollNumber and rownum = 1 )
FROM TABLEA a

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in mysql:
UPDATE TableA a, TableB b
  SET a.Name = b.Name
WHERE B.RollNumber = a.RollNumber

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>i mean database independent
no way, sorry.

the best is you put that code in a stored procedure, so "all" you have to do to make the application "portable" is to change/adjust the stored procedures ...
0
mandeliaAuthor Commented:
but B.RollNumber = a.RollNumber will return 3 rows
wont it create a problem
 
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no.
0
mandeliaAuthor Commented:
tried  ....
 
UPDATE [TABLEA]
SET NAMEA = DLOOKUP ("NAMEB","TABLEB". "rollB =" & rollA)
 
as column names in table a are rollA and NameA
and in tableB are nameB and rollB
 
 
It gives me invalid use of '.','!' or '()'. in query expression 'DLOOKUP ("NAMEB","TABLEB". "rollB =" & rollA'.
0
mandeliaAuthor Commented:
i have more than 1M rows so using update and Dlookup in VBA is out of question.
i think it will take a lot of time
0
mandeliaAuthor Commented:
sorry my fault there was a . insteed of , after tableB.
 
It worked
0
mandeliaAuthor Commented:
Brilliant
0
mandeliaAuthor Commented:
The query ran fine but didnot update giving a type conversion error.  All fields here is text any idea what can it be
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> All fields here is text any idea what can it be
UPDATE [TABLEA]
SET NAMEA = DLOOKUP ("NAMEB","TABLEB". "rollB = """ & rollA & """ ") 
  

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.