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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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...
please clarify
note: please do not post with ALL CAPS ON...
that's usually considered shouting...
ASKER
right now MS Access.
ASKER
oh ok that was not intentional
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the frist part how do in do this in MS SQL server 2005.
ASKER
moreover is there a query that can do the same on multiple servers
ASKER
i mean database independent
in sql, this would be:
UPDATE a
SET Name = b.Name
FROM TABLEA a
JOIN TableB b
on B.RollNumber = a.RollNumber
in oracle:
UPDATE a
SET Name = (SELECT b.Name FROM TableB b WHERE B.RollNumber = a.RollNumber and rownum = 1 )
FROM TABLEA a
in mysql:
UPDATE TableA a, TableB b
SET a.Name = b.Name
WHERE B.RollNumber = a.RollNumber
>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 ...
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 ...
ASKER
but B.RollNumber = a.RollNumber will return 3 rows
wont it create a problem
wont it create a problem
ASKER
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'.
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'.
ASKER
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
i think it will take a lot of time
ASKER
sorry my fault there was a . insteed of , after tableB.
It worked
It worked
ASKER
Brilliant
ASKER
The query ran fine but didnot update giving a type conversion error. All fields here is text any idea what can it be
> All fields here is text any idea what can it be
UPDATE [TABLEA]
SET NAMEA = DLOOKUP ("NAMEB","TABLEB". "rollB = """ & rollA & """ ")
ASKER