Help with update query please!

[mo]

I need to bring the ssn's into UniqueSups (supervisors) from
tblNonNormalize d. 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 tblNonNormalize d ON UniqueSups.NAME =
tblNonNormalize d.SupervisorNam e SET UniqueSups.SSN =
[tblNonNormalize d].[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]

Re: Help with update query please!

Try:

UPDATE UniqueSups
SET ssn =
(SELECT DISTINCT N.ssn
FROM tblNonNormalize d AS N
WHERE N.supervisornam e = 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@hotmail.com]

Re: Help with update query please!


mo wrote:[color=blue]
> I need to bring the ssn's into UniqueSups (supervisors) from
> tblNonNormalize d. 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 tblNonNormalize d ON UniqueSups.NAME =
> tblNonNormalize d.SupervisorNam e SET UniqueSups.SSN =
> [tblNonNormalize d].[SSN];
>
> but the SSNs are not populating. I'm not quite up to speed on the
> syntax for a union query.
> TIA
> Moe[/color]

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]

Re: Help with update query please!

Further clarification:
I am seeking to normalize the data.
tblNonNormalize d: (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 =, !=, <, <= ,[color=blue]
>, >= or when the subquery is used as an expression.[/color]
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 tblNonNormalize d AS N
WHERE N.supervisornam e = UniqueSups.name
AND N.ssn IS NOT NULL)


pietlinden@hotm ail.com wrote:[color=blue]
> mo wrote:[color=green]
> > I need to bring the ssn's into UniqueSups (supervisors) from
> > tblNonNormalize d. 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[/color][/color]
quite[color=blue][color=green]
> > get it.
> >
> > I tried:
> > UPDATE UniqueSups LEFT JOIN tblNonNormalize d ON UniqueSups.NAME =
> > tblNonNormalize d.SupervisorNam e SET UniqueSups.SSN =
> > [tblNonNormalize d].[SSN];
> >
> > but the SSNs are not populating. I'm not quite up to speed on the
> > syntax for a union query.
> > TIA
> > Moe[/color]
>
> 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[/color]
of[color=blue]
> the database structure and what you need to do with the data.[/color]
Whether[color=blue]
> you normalize will depend on several factors, and without knowing[/color]
some[color=blue]
> 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...[/color]

[David Portas]

Re: Help with update query please!

> It does not work in SQL Server because "Subquery returned more than 1
[color=blue]
> value. This is not permitted when the subquery follows =, !=, <, <= ,[/color]
[color=blue]
>, >= or when the subquery is used as an expression.[/color]

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

SELECT *
FROM tblNonNormalize d
WHERE supervisorname IN
(SELECT supervisorname
FROM tblNonNormalize d
GROUP BY supervisorname
HAVING MIN(ssn)<MAX(ss n))

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]

Re: Help with update query please!

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 UniqueSuperviso rs
SET UniqueSuperviso rs.SSN =
(Select Distinct [tblNonNormal].[SSN]
FROM tblNonNormal
WHERE UniqueSuperviso rs.SupervisorEm ail = tblNonNormal.Em ail)
Any thoughts why it won't run? tia Moe

David Portas wrote:[color=blue][color=green]
> > It does not work in SQL Server because "Subquery returned more than[/color][/color]
1[color=blue]
>[color=green]
> > value. This is not permitted when the subquery follows =, !=, <, <=[/color][/color]
,[color=blue]
>[color=green]
> >, >= or when the subquery is used as an expression.[/color]
>
> That's because you have more than one matching SSN for a given name[/color]
in[color=blue]
> tblNonNormalize d. In other words you need to clean up your data[/color]
first.[color=blue]
> Try this query:
>
> SELECT *
> FROM tblNonNormalize d
> WHERE supervisorname IN
> (SELECT supervisorname
> FROM tblNonNormalize d
> GROUP BY supervisorname
> HAVING MIN(ssn)<MAX(ss n))
>
> 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
> --[/color]

[David Portas]

Re: Help with update query please!

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
--