Solved

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

Posted on 2009-05-15
20
210 Views
Last Modified: 2012-05-07
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



0
Comment
Question by:mandelia
  • 12
  • 8
20 Comments
 
LVL 5

Author Comment

by:mandelia
Comment Utility
Dont worry the names are not not different and even if they are something like  First(name)  will solve my purpose
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 5

Author Comment

by:mandelia
Comment Utility
right now MS Access.
 
0
 
LVL 5

Author Comment

by:mandelia
Comment Utility
oh ok that was not intentional
 
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
what about this
UPDATE TABLEA

  SET NAME = DLOOKUP("NAME", "TABLEB", "ROLLNUMBER = " & ROLLNUMBER)

Open in new window

0
 
LVL 5

Author Comment

by:mandelia
Comment Utility
thanks for the frist part how do in do this in MS SQL  server 2005.
0
 
LVL 5

Author Comment

by:mandelia
Comment Utility
moreover is there a query that can do the same on multiple servers
0
 
LVL 5

Author Comment

by:mandelia
Comment Utility
i mean database independent
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
in mysql:
UPDATE TableA a, TableB b

  SET a.Name = b.Name

WHERE B.RollNumber = a.RollNumber

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 5

Author Comment

by:mandelia
Comment Utility
but B.RollNumber = a.RollNumber will return 3 rows
wont it create a problem
 
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
no.
0
 
LVL 5

Author Comment

by:mandelia
Comment Utility
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
 
LVL 5

Author Comment

by:mandelia
Comment Utility
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
 
LVL 5

Author Comment

by:mandelia
Comment Utility
sorry my fault there was a . insteed of , after tableB.
 
It worked
0
 
LVL 5

Author Closing Comment

by:mandelia
Comment Utility
Brilliant
0
 
LVL 5

Author Comment

by:mandelia
Comment Utility
The query ran fine but didnot update giving a type conversion error.  All fields here is text any idea what can it be
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
> 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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now