Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update Query

Author  Topic 

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-29 : 07:50:45
I have Tables A and B

Table_A:
+--------+-----------+
| Cust_ID| Name |
| 1 | Mark |
| 2 | Jane |
| 3 | James |
|+-------+-----------+

Table_B:
+--------+--------------------+
| Cust_ID| Email |
| 1 | mark@yahoo.com |
| 2 | jane@hotmail.com |
| 3 | |
| | alex@hotmail.com |
+--------+--------------------+


I need to update Table_A with fields in Table_B with this result:

+--------+-----------+------------------+
| Cust_ID| Name | Email |
| 1 | Mark | mark@yahoo.com |
| 2 | Jane | jane@hotmail.com |
| 3 | James | |
| 4 | | alex@hotmail.com |
|+-------+-----------+------------------+

UPDATE TABLE_A LEFT JOIN TABLE_B ON TABLE_A.CUST_ID= TABLE_B.CUST_ID

I do not think this one will work. There seems to be some error with
it.

Also, my table A and B are very big in size. I am afraid system will
hang since there are over thousands of data. To prevent this, I will like to add a column to Table B call 'Flag'. When the data is successfully transfered, flag= 1. Else, flag= null. Hence this way, even if system hangs, I am aware of what data is being transferred, what is not.

I do not think I can add an If-else statement in the SQL query. If so, how do I ensure that data is being transferred over before changing flag= 1?

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2004-08-29 : 08:04:15
Do you have an Email field in Table_A ?
This updates only the rows where Email in Table_A
is null or different than Email in Table_B.
Thousands of data is not big.
Hundreds of Thousands is medium.
And millions is big.

UPDATE Table_A SET
	Email = b.Email
FROM
	Table_A
	JOIN Table_B b
		ON Table_A.Cust_ID = b.Cust_ID
WHERE
	ISNULL( Table_A.Email, '' ) <> b.Email


Or do you need a view of the a & b combined ?
SELECT
	a.Cust_ID,
	a.Name,
	b.Email
FROM
	Table_A
	/*LEFT*/ JOIN Table_B b
		ON Table_A.Cust_ID = b.Cust_ID


Storing Email in both Table_A & Table_B is not a normalized database design.

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-29 : 22:06:02
Actually I have a lot of columns in Table A and B. If I do it as your recommendation, I will have to key in my column names one by one?

Also, my Table A alreadi has an Email column, but it is null. Table B has the Email column with information. Hence, I need to update Table A with information in Table B.

UPDATE TABLE_A LEFT JOIN TABLE_B
ON TABLE_A.Cust_ID = TABLE_B.Cust_ID
WHERE ISNULL( Table_A.Email, '' ) <> TABLE_B.Email;

But once again, I have multiple columns. Do I have to key them in one by one?
Go to Top of Page

timmy
Master Smack Fu Yak Hacker

1242 Posts
Posted - 2004-08-29 : 23:29:50
If you're updating only the one field, you only need to include that one in your query.
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-29 : 23:34:06
I am updating a lot of columns, in actual fact.

So do I have to change the following

WHERE ISNULL( Table_A.Email, '' ) <> TABLE_B.Email;

to

WHERE ISNULL( Table_A.Email, '' ) <> TABLE_B.Email;
WHERE ISNULL( Table_A.Zip, '' ) <> TABLE_B.Zip;
WHERE ISNULL( Table_A.Tel, '' ) <> TABLE_B.Tel;
WHERE ISNULL( Table_A.Mobile, '' ) <> TABLE_B.Mobile;

I will like to update Table_A with Zip, Tel and Mobile from Table_B. So I have to type in the columns names one by one?
Go to Top of Page

timmy
Master Smack Fu Yak Hacker

1242 Posts
Posted - 2004-08-29 : 23:44:59
Yes.


Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-29 : 23:58:23
Thanks u all for the help!

Thx Thx~!
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2004-08-30 : 01:01:15
Actually for multicolumn update,

UPDATE TABLE_A SET
	Email = TABLE_B.Email,
	Zip = TABLE_B.Zip,
	Tel = TABLE_B.Tel,
	Mobile = TABLE_B.Mobile
FROM
	TABLE_A
	JOIN TABLE_B ON TABLE_A.Cust_ID = TABLE_B.Cust_ID
WHERE
	ISNULL( TABLE_A.Email, '' ) <> TABLE_B.Email
	OR ISNULL( TABLE_A.Zip, '' ) <> TABLE_B.Zip
	OR ISNULL( TABLE_A.Tel, '' ) <> TABLE_B.Tel
	OR ISNULL( TABLE_.Mobile, '' ) <> TABLE_B.Mobile


But I guess You already worked this out.
You don't need the LEFT JOIN in the update because only matched records will be updated anyways.

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-30 : 01:28:37
Thank You SoOoOo Much~!!! ^_^
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-30 : 02:02:03
rockmoose, regarding the codes you gave. It updates the fields in Table_A is they are Null, or Different right?
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2004-08-30 : 03:06:44
Yes, If they are null or different.

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-31 : 01:08:44
I run the query as below.

UPDATE ocma_2900113_PH_UTF8_8_27_2004_7000

SET
addr_nm = ocma_2900114_PH_UTF8_8_27_2004.addr_nm,
addr_city_nm = ocma_2900114_PH_UTF8_8_27_2004.addr_city_nm,
zip_postal_cd = ocma_2900114_PH_UTF8_8_27_2004.zip_postal_cd,
tele_country_access_cd = ocma_2900114_PH_UTF8_8_27_2004.tele_country_access_cd,
tele_city_cd = ocma_2900114_PH_UTF8_8_27_2004.tele_city_cd,
tele_phone_nr = ocma_2900114_PH_UTF8_8_27_2004.tele_phone_nr,
gender = ocma_2900114_PH_UTF8_8_27_2004.gender,
date_of_birth = ocma_2900114_PH_UTF8_8_27_2004.date_of_birth,
occupation = ocma_2900114_PH_UTF8_8_27_2004.occupation,
priv_phone_cd = ocma_2900114_PH_UTF8_8_27_2004.priv_phone_cd,
priv_postal_cd = ocma_2900114_PH_UTF8_8_27_2004.priv_postal_cd

FROM ocma_2900113_PH_UTF8_8_27_2004_7000 JOIN ocma_2900114_PH_UTF8_8_27_2004

ON ocma_2900113_PH_UTF8_8_27_2004_7000.pers_last_nm = ocma_2900114_PH_UTF8_8_27_2004.pers_last_nm

AND ocma_2900113_PH_UTF8_8_27_2004_7000.email_add_nm = ocma_2900114_PH_UTF8_8_27_2004.email_add_nm

WHERE
ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.addr_nm, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.addr_nm
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.addr_city_nm, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.addr_city_nm
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.zip_postal_cd, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.zip_postal_cd
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.tele_country_access_cd, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.tele_country_access_cd
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.tele_city_cd, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.tele_city_cd
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.tele_phone_nr, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.tele_phone_nr
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.gender, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.gender
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.date_of_birth, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.date_of_birth
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.occupation, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.occupation
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.priv_phone_cd, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.priv_phone_cd
OR ISNULL( ocma_2900113_PH_UTF8_8_27_2004_7000.priv_postal_cd, '' ) <> ocma_2900114_PH_UTF8_8_27_2004.priv_postal_cd

The table to be updated is: ocma_2900113_PH_UTF8_8_27_2004_7000
The table to retrieve information from is: ocma_2900114_PH_UTF8_8_27_2004

There are two columns used as "keys". They are: email_add_nm AND pers_last_nm.

I used the below query to check how many queries was supposed to be update.

SELECT COUNT(*)
AS Expr1
FROM ocma_2900114_PH_UTF8_8_27_2004
WHERE ((EMAIL_ADD_NM + PERS_LAST_NM) IN
(SELECT email_add_nm + pers_last_nm
FROM Ocma_2900113_PH_UTF8_8_27_2004_7000))

I should have 5013 records to be update. Yet, 5116 rows were affected instead. Why is it so?
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2004-08-31 : 01:33:58
Run the update again, and see how many records are affected.


rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-31 : 02:02:07
I run it again. It gave the same answer. 5116 row(s) affected.
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-31 : 02:42:08
Is there something wrong with the query I used to check?

If not, what is the actual problem?

I tried the same query on other tables and it gave me the same type of result.

On one table, I was supposed to update 9412, but I affected 9176 instead.
Another one, I was supposed to update 239, but I affected 256 rows instead.

Really appreciate help...
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2004-08-31 : 04:17:15
Question:
Is email_add_nm AND pers_last_nm UNIQUE ?
Does either column allow nulls ?

This will count nr of rows and nr of distinct rows from tables ocma_2900114_PH_UTF8_8_27_2004, Ocma_2900113_PH_UTF8_8_27_2004_7000
SELECT
( SELECT COUNT(*) FROM ocma_2900114_PH_UTF8_8_27_2004 ) AS cnt_2004,
( SELECT COUNT(*) FROM Ocma_2900113_PH_UTF8_8_27_2004_7000 ) AS cnt_2004_7000,
( SELECT COUNT(*) FROM ( SELECT DISTINCT email_add_nm, pers_last_nm FROM ocma_2900114_PH_UTF8_8_27_2004 ) t1 ) AS distcnt_2004,
( SELECT COUNT(*) FROM ( SELECT DISTINCT email_add_nm, pers_last_nm FROM Ocma_2900113_PH_UTF8_8_27_2004_7000 ) t2 ) AS distcnt_2004_7000


quote:
Is there something wrong with the query I used to check?
Well, it is kindof the wrong way round, ( it counts the nr of rows in ocma_2900114_PH_UTF8_8_27_2004 that would be updated from Ocma_2900113_PH_UTF8_8_27_2004_7000.

If I were getting the kind of results you are, and I were'nt expecting it
I would start to check the data in the tables, to see what's in there...

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-31 : 04:32:22
According to the query above, I came to this table.

cnt_2004: 5046
cnt_2004_7000: 7603
distcnt_2004: 4665
distcnt_2004_7000:6889

What does that mean? I am lost.



WoooF~
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2004-08-31 : 04:44:49
Well it means:
1. email_add_nm AND pers_last_nm is NOT UNIQUE,
this means that when You join on these 2 columns you can get multiple hits
2. There is not the same number of records in the two tables( 5046 - 7603 ),
if You expected otherwise you have a problem, otherwise ok.

Run aquery on the ocma_2900114_PH_UTF8_8_27_2004 table:
( this will givwe the records with duplicates in email_add_nm AND pers_last_nm )
SELECT t.*
FROM
	ocma_2900114_PH_UTF8_8_27_2004 t
	JOIN ( 	SELECT email_add_nm, pers_last_nm
		FROM ocma_2900114_PH_UTF8_8_27_2004
		GROUP BY email_add_nm, pers_last_nm
		HAVING COUNT(*) > 1 ) AS duplicates
	ON t.email_add_nm = duplicates.email_add_nm
	AND t.pers_last_nm = duplicates.pers_last_nm
ORDER BY
	t.email_add_nm,
	t.pers_last_nm


Question: What is the PRIMARY KEY in the two tables ?




rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-08-31 : 21:38:15
SELECT t.*
FROM ocma_2900114_PH_UTF8_8_27_2004 t INNER JOIN
(SELECT email_add_nm, pers_last_nm
FROM ocma_2900114_PH_UTF8_8_27_2004
GROUP BY email_add_nm, pers_last_nm
HAVING COUNT(*) > 1) duplicates ON t.EMAIL_ADD_NM = duplicates.email_add_nm AND t.PERS_LAST_NM = duplicates.pers_last_nm
ORDER BY t.EMAIL_ADD_NM, t.PERS_LAST_NM

This query really helps alot. It shows the duplicated records I have in my table. However I will like to transform the result from :

Name-Email----------Zip
abc abc@hotmail.com 4000
abc abc@hotmail.com 4000
abc abc@hotmail.com 3800
def def@hotmail.com 4000
def def@hotmail.com 4000

to:

abc abc@htomail.com 4000
abc abc@hotmail.com 3800
def def@hotmail.com 4000

If I add a DISTINCT in front (of t.*), will it help?


- HELP -
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2004-09-01 : 03:14:19
Hi,

If you just do DISTINCT Name, Email, Zip ...
you will get the result above.
If there are other columns involved, No, since it will give the distinct combination of all columns involved.

Now, if You are NOT supposed to have duplicate records in the table
then You MUST clean the data, otherwise You will have problems all the way.
Is that the case ?

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts
Posted - 2004-09-01 : 04:35:58
I manage to get everything now, THANKS~!

- HELP -
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site