473,327 Members | 1,919 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

Nov 13 '05 #1
6 1125
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
--

Nov 13 '05 #2

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

Nov 13 '05 #3
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...


Nov 13 '05 #4
> 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
--

Nov 13 '05 #5
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
--


Nov 13 '05 #6
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
--

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Somerset Bob | last post by:
I've posted my query at the phpbb forum, where I got half an answer and then no more. I posted it again at another phpbb forum, where I got half an answer and no more. I posted it in alt.php, where...
0
by: Somerset Bob | last post by:
I've posted my query at the phpbb forum, where I got half an answer and then no more. I posted it again at another phpbb forum, where I got half an answer and no more. I posted it in alt.php, where...
6
by: John Baker | last post by:
Hi: As those who have looked at this newsgroup recently will realize, I am a neophyte with Access, although I have experienced with Approach (the Lotus product). There are things I could easily...
9
by: hope | last post by:
Hi Access 97 I'm lost on this code please can you help ================================= Below is some simple code that will concatenate a single field's value from multiple records into a...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
3
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all...
6
by: Takeadoe | last post by:
Dear NG, Can someone assist me with writing the little code that is needed to run an update table query each time the database is opened? From what I've been able to glean from this group, the...
17
by: so many sites so little time | last post by:
all right so the script is pretty simple it goes it retrives what the id of the post is and it lets you edit it well no it doesnt. now if you go to www.kirewire.com/pp2/index/php you will see a...
1
by: dee | last post by:
I have a table 'LeadHistory' which has among others, the following fields. Salesman(Text) SalesmanAssmntDate(Date/Time) Disposition(Text) I also have a table 'LookUpSalesman' which has among...
4
by: pankajsingh5k | last post by:
Hi guys, These question is for all the experts... Please help me before my brain explodes The problem is again with the formview control.. I have a formview and i have to use it that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.