Help with update query please!

[mo]

I need to bring the ssn's into UniqueSups (supervisors) from
tblNonNormalized. My inherited DB is not normalized and I find it
extremely irritating due to the workarounds needed.
I created tblUniqueSups by doing a select Distinct Supervisor Name.
Now I need to bring in the SSNs of the Unique Sups but I can't quite
get it.

I tried:
UPDATE UniqueSups LEFT JOIN tblNonNormalized ON UniqueSups.NAME =
tblNonNormalized.SupervisorName SET UniqueSups.SSN =
[tblNonNormalized].[SSN];

but the SSNs are not populating. I'm not quite up to speed on the
syntax for a union query.
TIA
Moe

[David Portas]

Try:

UPDATE UniqueSups
SET ssn =
(SELECT DISTINCT N.ssn
FROM tblNonNormalized AS N
WHERE N.supervisorname = UniqueSups.name
AND N.ssn IS NOT NULL)

This can only work if you have a single SSN for each unique name in
your non-normalized table. Otherwise you'll get an error and you'll
have to do some more data cleansing.

--
David Portas
SQL Server MVP
--

[pietlinden]

mo wrote:

I need to bring the ssn's into UniqueSups (supervisors) from
tblNonNormalized. My inherited DB is not normalized and I find it
extremely irritating due to the workarounds needed.
I created tblUniqueSups by doing a select Distinct Supervisor Name.
Now I need to bring in the SSNs of the Unique Sups but I can't quite
get it.

I tried:
UPDATE UniqueSups LEFT JOIN tblNonNormalized ON UniqueSups.NAME =
tblNonNormalized.SupervisorName SET UniqueSups.SSN =
[tblNonNormalized].[SSN];

but the SSNs are not populating. I'm not quite up to speed on the
syntax for a union query.
TIA
Moe

Huh? Post your table structure. field names? types? meanings?

union queries are about as hard as falling down. The *only* trick is
that you need union-compatible fields (generally of the same type).

Say you have two tables, tblA and tblB, with structures like this:

CREATE TABLE tblA(
SSN Text(9) PRIMARY KEY,
Firstname Text(20),
Lastname Text(25),
....
)

and

CREATE TABLE tblB(
SocSecNo Text(9) PRIMARY KEY,
FName Text(20),
LName Text(25),
....
)

Union is no big deal - you just have to alias the fields in one table
so they map right...

SELECT SSN, FirstName, LastName
FROM tblA
UNION ALL
SELECT SocSecNo as SSN, FName as FirstName, LName as LastName
FROM tblB
ORDER BY SSN;

(aliasing is the [FieldName] AS (alias) stuff.

IF the database really is not normalized, you *may* need to normalize
it to get it to work... so you might want to post the relevant parts of
the database structure and what you need to do with the data. Whether
you normalize will depend on several factors, and without knowing some
more about the thing, it's hard to tell what to advise. Could be a
huge undertaking for very little payoff... but then it might be worth
it or relatively painless...

[mo]

Further clarification:
I am seeking to normalize the data.
tblNonNormalized: (Employees and supervisors)
Name SSN Supervisor
Jon 222 Ed
Sam 333 Ed
Ed 444 Tom
destination: UniqueSups (created from Create Table Distinct
SupervisorName)
Ed 444
Tom 555

They've (read: previous idiot designer) placed children and parents in
the same table! I am trying to extract the unique supervisors into a
new table. My new table would contain supervisorname Ed and his SSN
just once rather than one for every person he supervises. The plain
old falling off a log update query is not working, as it is copying all
the ssns rather than just those where it is a supervisor record.
(Sorry to obfuscate, I was trying to simplify the problem.) The
suggested query from David Portas, thank you btw, does not work in
Access "requires [UPDATE], [DELETE] etc."
It does not work in SQL Server because "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,

, >= or when the subquery is used as an expression. The statement has been terminated." This syntax does work on other
normaized tables that I created.

UPDATE UniqueSups
SET ssn =
(SELECT DISTINCT N.ssn
FROM tblNonNormalized AS N
WHERE N.supervisorname = UniqueSups.name
AND N.ssn IS NOT NULL)
pi********@hotmail.com wrote: mo wrote:

I need to bring the ssn's into UniqueSups (supervisors) from
tblNonNormalized. My inherited DB is not normalized and I find it
extremely irritating due to the workarounds needed.
I created tblUniqueSups by doing a select Distinct Supervisor Name.
Now I need to bring in the SSNs of the Unique Sups but I can't quite get it.

I tried:
UPDATE UniqueSups LEFT JOIN tblNonNormalized ON UniqueSups.NAME =
tblNonNormalized.SupervisorName SET UniqueSups.SSN =
[tblNonNormalized].[SSN];

but the SSNs are not populating. I'm not quite up to speed on the
syntax for a union query.
TIA
Moe
Huh? Post your table structure. field names? types? meanings?

union queries are about as hard as falling down. The *only* trick is
that you need union-compatible fields (generally of the same type).

Say you have two tables, tblA and tblB, with structures like this:

CREATE TABLE tblA(
SSN Text(9) PRIMARY KEY,
Firstname Text(20),
Lastname Text(25),
...
)

and

CREATE TABLE tblB(
SocSecNo Text(9) PRIMARY KEY,
FName Text(20),
LName Text(25),
...
)

Union is no big deal - you just have to alias the fields in one table
so they map right...

SELECT SSN, FirstName, LastName
FROM tblA
UNION ALL
SELECT SocSecNo as SSN, FName as FirstName, LName as LastName
FROM tblB
ORDER BY SSN;

(aliasing is the [FieldName] AS (alias) stuff.

IF the database really is not normalized, you *may* need to normalize
it to get it to work... so you might want to post the relevant parts

of the database structure and what you need to do with the data. Whether you normalize will depend on several factors, and without knowing some more about the thing, it's hard to tell what to advise. Could be a
huge undertaking for very little payoff... but then it might be worth
it or relatively painless...

[David Portas]

> It does not work in SQL Server because "Subquery returned more than 1

value. This is not permitted when the subquery follows =, !=, <, <= , , >= or when the subquery is used as an expression.

That's because you have more than one matching SSN for a given name in
tblNonNormalized. In other words you need to clean up your data first.
Try this query:

SELECT *
FROM tblNonNormalized
WHERE supervisorname IN
(SELECT supervisorname
FROM tblNonNormalized
GROUP BY supervisorname
HAVING MIN(ssn)<MAX(ssn))

Then decide what you want to do about the duplicate names with
different SSNs. If you need more help, please post again with CREATE
TABLE statements for your tables and a few rows of sample data as
INSERT statements so that we can reproduce your problem.

--
David Portas
SQL Server MVP
--

[mo]

Thank you very much David, cleaning the data is the goal.
This query actually succeeds in updating the appropriate SSNs in SQL
Server, but it doesn't work in Access, where I'm tasked to work. In
Access 2002, I'm still getting the 'must be an updateable query' even
though tblNonNormal is an attached SQL server table and the one I'm
writing to is a local Access one.
UPDATE UniqueSupervisors
SET UniqueSupervisors.SSN =
(Select Distinct [tblNonNormal].[SSN]
FROM tblNonNormal
WHERE UniqueSupervisors.SupervisorEmail = tblNonNormal.Email)
Any thoughts why it won't run? tia Moe

David Portas wrote:

It does not work in SQL Server because "Subquery returned more than

1

value. This is not permitted when the subquery follows =, !=, <, <=

,

, >= or when the subquery is used as an expression.
That's because you have more than one matching SSN for a given name

in tblNonNormalized. In other words you need to clean up your data first. Try this query:

SELECT *
FROM tblNonNormalized
WHERE supervisorname IN
(SELECT supervisorname
FROM tblNonNormalized
GROUP BY supervisorname
HAVING MIN(ssn)<MAX(ssn))

Then decide what you want to do about the duplicate names with
different SSNs. If you need more help, please post again with CREATE
TABLE statements for your tables and a few rows of sample data as
INSERT statements so that we can reproduce your problem.

--
David Portas
SQL Server MVP
--

[David Portas]

This is a SQL Server group . You'll probably get better help in an
Access group. Alternatively, run it as a pass-though query.

--
David Portas
SQL Server MVP
--