Update or Append? and how to accomplish this? (1 Viewer)

[Espinoza84]

Hello everyone,

I have 2x tables:

1) TBL_EMPLOYEES
this table has 800 records (employees), 200 of these records have null value under the "e-mail" column

2) TBL_EMPLOYEES_MISSING_EMAILS
This table has, 200 records, these 200 records are the ones from table 1 that with missing emails.

* both tables have exact same column names and count.

* the data of the 200 rows in table2 is the same as the data of these same 200 rows in table 1, the only exception is that in table 2 the email column for these 200 employees is not null.

My Goal:
I want to 'fix' Table 1. I want to update the emails from table 2 to table 1 for the 200x employees that have missing emails.

Questions:

A) Do I accomplish this through an Update or Append Query?

B) If so, then how do I go about to do this?

<-- Big Update/Append roookie here.

Thank you all.

 

[rainman89]

You could do an insert Into, and link the employee IDs together and where emp email is null

http://www.1keydata.com/sql/sqlinsert.html

see if that gives u an idea

 

[georgedwilkinson]

update table1 
inner join table2 on 
table1.whateveriscommon = table2.whateveriscommong
set table1.[e-mail] = table2.[e-mail];

Best not to use special characters in database object/column names.

 

[MSAccessRookie]

In order for you to identifuy the records that need to be updated, and pair them with the records that contain the missing data, there MUST be some common Link, or SQL cannot do the pairing for you.

QUESTIONS:

• Is there a Common Person ID # that can be used?
  • Even if the Person ID # has a different name in each table, it can be used if the same numbers refer to the same people.
• Do the tables have any other items in common?
  • You can use these fields instead
• Are you willing to enter the common information?
  • You can create a third table that has three columns and use it as a bridge.
    • ID Number from table 1
    • Unique field from table 2
    • Email from table2

 

[Espinoza84]

USERID is what is the 'common' and 'unique' data for each employee

 

[rainman89]

Then use userID where email isnull

 

[georgedwilkinson]

In that case:

update table1 
inner join table2 on 
table1.USERID = table2.USERID
set table1.[e-mail] = table2.[e-mail];

 

[MSAccessRookie]

One answer might be a combination of all of the suggestions:

update table1 
inner join table2 on 
table1.userid= table2.userid
where table1.[e-mail] is NULL
set table1.[e-mail] = table2.[e-mail];

Also, getting rid of special characters (e-mail to email) is always a good idea

POSTSCRIPT:

I noticed that once again I replied too late and I said the same thing as georgewilkinson, but this time I included the "where table1.[e-mail] is NULL" while he did not. Was this line unnecessary?

 

[sbenj69]

I'm enclosing a database that has both append and update queries.

The append query searches the main table and appends them to a table for you to enter in the missing emails.

The update query then updates the emails you added to the main table.

An answer to your question would be...... use an update query to update the actual data....... the append query merely adds records to a table.... if you take the primary key off the updatetbl and keep running appends it will add duplicate records. If you keep the primary key on, an append query will give you a warning stating primary key violations, blah blah blah.

So, for example, you remove the primary key from UpdateTbl, you can keep on appending and bloating the database.

 

[rainman89]

One answer might be a combination of all of the suggestions:

update table1 
inner join table2 on 
table1.userid= table2.userid
where table1.[e-mail] is NULL
set table1.[e-mail] = table2.[e-mail];

Also, getting rid of special characters (e-mail to email) is always a good idea

POSTSCRIPT:

I noticed that once again I replied too late and I said the same thing as georgewilkinson, but this time I included the "where table1.[e-mail] is NULL" while he did not. Was this line unnecessary?

I only added that incase the other table didnt have ALL email addresses filled. IF it only had the users that were missing from before then it is not neccesary, but say that it had all userIDs and blanks in there for the emails that were there before, it would overwrite the old ones with the blanks.... not good!!

 

[MSAccessRookie]

I think this works, but my DB is read only and I cannot test it.

update UpdateTable 
inner join table1 on 
UpdateTable.MyAutonum= Table1.MyAutonum
set Table1.MyEmail = updatetable.MyEmail
where Table1.MyEmail is NULL;

George Wilkinson:

I notice that you do a lot of assistance via downloaded samples. Mine always come out read only. any way for me to change this? NOTE: I am using Access 2003.

 

[sbenj69]

hmmmm, shouldn't be read only......

Are you opening it directly from the zip? I run into that problem (by running the zip directly from the link, and then open the file) I find if you download to your pc, extract, and then open the extracted db, this alleviates that problem (that or you might have to set macro security lower)

 

[MSAccessRookie]

hmmmm, shouldn't be read only......

Are you opening it directly from the zip? I run into that problem (by running the zip directly from the link, and then open the file) I find if you download to your pc, extract, and then open the extracted db, this alleviates that problem (that or you might have to set macro security lower)

That was what I needed. Extracting the file took away the read only attribute. Thanks for the tip.

 

[Espinoza84]

thank you all btw,

I used George's example and got it to work.

Thanks.

 

[sbenj69]

I think the reason it opens up read-only the other way is because it cannot open a locking file.

 

[Espinoza84]

Tx for the tip: ill try to stear away from special characters in column names.

 

[MSAccessRookie]

I opened the extracted database, and ran the query only to find that it did not work. Further inspection shows that it could not have worked because neither table had complete data, and in fact, one of them was blank.

I believed that Geroge Wilkinsons procedure assumed that your update table had all users and all emails and therefore would not work as written. Reevaluating my thinking, I decided that the "IS Null" would not be necessary if the table ONLY contained values for the Null records.

 

[Espinoza84]

correct sir, the second table only contains the 'rows' of the employees that had missing emails on table 1.

so: table 1: 800 rows (200 rows are employees that have missing email)
table 2: 200 rows (the 200 rows of employees from table1 with missing email)

 

[MSAccessRookie]

Tx for the tip: ill try to stear away from special characters in column names.

Special characters are generally deemed to be any characters that are not Letters (upper or lower case) or numbers, including spaces.

Note: Access itself does not seem to mind most of them, but if you ever need to or want to upgrade or convert to something like Oracle or SQL Server... Be prepared for a bumpy ride

 

[Espinoza84]

ohhh great bit of info there.

In the future here at my work-place we are planning on transfering this database over to ms sql. Since, I am in the process of editing this database currently, I will make sure all table-columns do not have special characters.