Running an Update Subquery

[Guest]

Is it possible to run an update statement with a subquery? What is the syntax?

Say you have emp and dept tables.

update emp set emp.depdesc = select dept.description from dept where emp.deptno
= dept.deptno

In other words I want to put values in preexisting records based on another
table. If dept has a description, then I want emp to have fname, lname, deptno,
deptdescription.

Is that possible?
(e-mail address removed)

 

[Nikos Yannacopoulos]

It is possible to do it either by linking the two tables in a query, or by
using a DLookup function.

It is also USELESS as well as BAD PRACTICE, defying the whole idea of
relational databases, i.e. no duplication of data! You can always retrieve
the department name from the departments table in the same way whenever you
need it, so why multi-store in the employees table?

HTH,
Nikos

 

[Guest]

This is an org with no IT department. You can't always do things in a logical
way, with proper constraints. You have to do what non IT people ask you to do.
The match table shows two people match date, end date, the Home table shows
individuals, both seekers and providers, with about 100 fields, so a match date
field shows when and if they've been matched. But as I've stated earlier, they
actually have people in more than one record, reentering them instead of
updating. So anything, queries, reports means its difficult to get single
records when first and last names repeat. Its a bit maddening but the way they
want it. This is an org. Not AT&T. And with the Director a stuborn 65 year old
woman who can't even use word, just access forms someone else built, explaining
why they need more constraints, single records, etc is difficult to say the
least.

One of the things they want is when they make matches, to automatically update
info in the Home table.


(e-mail address removed)