Help with update query linking 3 tables

[rdraider]

I am looking for some assistance with an update query that needs to link 3
tables:

This query ran and reported over 230,000 records affected but did not change
the field I wanted changed, not sure what it did.
I did notice that the "name" in "GM_NAMES.name" was colored blue in Query
Analyzer. Is it bad to name a column "name"?

UPDATE ABSENCES
set CustomerContactID = cicntp.cnt_id
from absences, cicntp
where (SELECT cicntp.cnt_l_name
FROM cicntp INNER JOIN
gm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOIN
Absences ON cicntp.cnt_id = Absences.CustomerContactID)

Next I tried this query which is still running after 75 minutes (on a
laptop)

update absences
set CustomerContactID = cicntp.cnt_id
from absences, cicntp, gm_names
where gm_names.name= cicntp.cnt_l_name
As you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.
Absences.CustomerContactID is what I need updated, when finished it should
match CICNTP.cnt_id
GM_NAMES is a temp table and matches records in CICNTP.cnt_l_name

Can some of you school this newbie on the best way to do this?
Thanks a bunch!

[Hugo Kornelis]

On Fri, 15 Apr 2005 19:54:56 GMT, rdraider wrote:

I am looking for some assistance with an update query that needs to link 3
tables:

This query ran and reported over 230,000 records affected but did not change
the field I wanted changed, not sure what it did.
I did notice that the "name" in "GM_NAMES.name" was colored blue in Query
Analyzer. Is it bad to name a column "name"?

UPDATE ABSENCES
set CustomerContactID = cicntp.cnt_id
from absences, cicntp
where (SELECT cicntp.cnt_l_name
FROM cicntp INNER JOIN
gm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOIN
Absences ON cicntp.cnt_id = Absences.CustomerContactID)
Hi rdraider,

I guess that you made a copy & paste error, since this query can never
have reported over 230,000 rows affected - it can only report "Incorrect
syntax near ')'".

Next I tried this query which is still running after 75 minutes (on a
laptop)

update absences
set CustomerContactID = cicntp.cnt_id
from absences, cicntp, gm_names
where gm_names.name= cicntp.cnt_l_name
Depending on the size of the tables, it'll probably run a whole lot
longer - and it likely won't result in the change you wish. Since you
didn't specify anything to link absences to either of the other tables,
this query will effectively:

1. Join cicnt and gm_names on the name column, as specified in the WHERE
clause; then
2. Update EACH row in absences with the value of EACH row in the result
of the join above. If that join yields a million rows, then each row in
absences will be updated a million times. And in the end, the cnt_id
value from whatever row happens to be processed last will end up being
in CustomerContactID for ALL absences!

As you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.
Absences.CustomerContactID is what I need updated, when finished it should
match CICNTP.cnt_id
GM_NAMES is a temp table and matches records in CICNTP.cnt_l_name
Can some of you school this newbie on the best way to do this?
Thanks a bunch!

Neither this narrative, nor any of the queries you posted does a very
good job at explaining what you need to get done. A much better way to
ask help in newsgroups is to post the table structure (as CREATE TABLE
statements), some rows of sample data (as INSERT statements) and the
expected output. More on this can be found at www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

1) Learn the Standard SQL UPDATE syntax, so you will not get Cartesian
explosions problems that will destroy your data integrity.

2) Avoid temp tables in favor of derived tables that the optimizer can
use.

3) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

4) Here is a wild guess. I would replace GM_names with a derived
table.

UPDATE Absenses
SET customer_contact_id
= (SELECT C1.cnt_id
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id)
WHERE EXISTS
(SELECT *
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id);

If the subquery expression returns no rows, you will get a NULL; If it
retursn more than one row, you will get a cardinality violation.

[Hugo Kornelis]

On 15 Apr 2005 15:21:03 -0700, --CELKO-- wrote:

4) Here is a wild guess. I would replace GM_names with a derived
table.

UPDATE Absenses
SET customer_contact_id
= (SELECT C1.cnt_id
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id)
WHERE EXISTS
(SELECT *
FROM Cicntp AS C1, GM_names AS G1
WHERE C1.cnt_l_name = G1.name
AND C1.cnt_id = Absences.customer_contact_id);

Hi Joe,

I was starting to post the exact same solution, but then I saw that this
version makes no sense at all - look at the way the subqueries are
joined to the table to be updated: the effect will be that
customer_contact_id will only be changed if it already has the correct
value. In other words: this UPDATE statement might affect some rows, but
it definitely won't change any data!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

Yes. But this is what he wrote when both of us (who are pretty good
SQL guys) trie to understand it. It would be nice to have some DDL and
clear specs instead of bad code ...