Solved

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

Posted on 2009-05-15
20
240 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
ID: 24396051
Dont worry the names are not not different and even if they are something like  First(name)  will solve my purpose
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24396055
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
ID: 24396196
right now MS Access.
 
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 5

Author Comment

by:mandelia
ID: 24396203
oh ok that was not intentional
 
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24396222
what about this
UPDATE TABLEA
  SET NAME = DLOOKUP("NAME", "TABLEB", "ROLLNUMBER = " & ROLLNUMBER)

Open in new window

0
 
LVL 5

Author Comment

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

Author Comment

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

Author Comment

by:mandelia
ID: 24396324
i mean database independent
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24396362
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24396367
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24396382
in mysql:
UPDATE TableA a, TableB b
  SET a.Name = b.Name
WHERE B.RollNumber = a.RollNumber

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24396398
>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
ID: 24396410
but B.RollNumber = a.RollNumber will return 3 rows
wont it create a problem
 
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24396483
no.
0
 
LVL 5

Author Comment

by:mandelia
ID: 24396608
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
ID: 24396740
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
ID: 24396778
sorry my fault there was a . insteed of , after tableB.
 
It worked
0
 
LVL 5

Author Closing Comment

by:mandelia
ID: 31581933
Brilliant
0
 
LVL 5

Author Comment

by:mandelia
ID: 24396886
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 143

Expert Comment

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
New and Previous Values in a Query 7 30
Need help how to find where my error is in UFD 6 40
Syntax for query to update table 2 29
Syntax Issue with SSIS module 26 104
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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