How do I update varying data into an existing column of a table?

[Guest]

I know how to run the various queries but I am stumped here.

How do I only update or append data to a column in an existing table where
the update/append varies?

What I mean by varies is that I am not trying to make/change all records to
one value, each record in the table currently has a field that is blank, each
record has a unique value and I would like to update the records in that
column.

Append Query won't let me since there are key violations due to the records
already existing and an Update Query expects me to enter one value....

Thank you, in advance, for your assistance!

 

[Guest]

Post a sample of what your data looks like now and what you would want it to
be as a result of the update.

When updating data you need to use criteria. You have to determine what
that criteria would be for those records that need to be updated. You
mentioned 'has a field that is blank' then NULL would be part or all of the
criteria.

 

[Guest]

Hi!

Existing Database Table (ODBC connection) is:

LocId Facility CustId Seq1 Zone
1AA16A ABC 1234 (null) ZN54
2BB18B ABC 1234 (null) ZN67
4DD27A ABC 1234 (null) ZN67

=== I have another table that has the Seq1 number in it with all of the
other exisiting Db fields. It is how I want the Existing Database Table
(above) to contain once it is updated.

Other table and/or the desired out put of the Existing Database Table (above)

LocId Facility CustId Seq1 Zone
1AA16A ABC 1234 11001 ZN54
2BB18B ABC 1234 12789 ZN67
4DD27A ABC 1234 10899 ZN67

*** All numbers in Seq1 are unique.

Thanks!

 

[Guest]

Backup your database and then use this query to update your Table A from data
in Table B. Substitute your actual table names.
UPDATE [Table A] INNER JOIN [Table B] ON ([Table A].Zone = [Table B].Zone)
AND ([Table A].CustId = [Table B].CustId) AND ([Table A].Facility = [Table
B].Facility) AND ([Table A].LocId = [Table B].LocId) SET [Table A].Seq1 =
[Table B].[Seq1]
WHERE ((([Table A].Seq1) Is Null));

 

[John W. Vinson]

I know how to run the various queries but I am stumped here.

How do I only update or append data to a column in an existing table where
the update/append varies?

What I mean by varies is that I am not trying to make/change all records to
one value, each record in the table currently has a field that is blank, each
record has a unique value and I would like to update the records in that
column.

Append Query won't let me since there are key violations due to the records
already existing and an Update Query expects me to enter one value....

You can update to an expression; it needn't be a constant.

How can you determine on the basis of the existing data what the NULL
field should become?

John W. Vinson [MVP]

 

[John W. Vinson]

Hi!

Existing Database Table (ODBC connection) is:

LocId Facility CustId Seq1 Zone
1AA16A ABC 1234 (null) ZN54
2BB18B ABC 1234 (null) ZN67
4DD27A ABC 1234 (null) ZN67

=== I have another table that has the Seq1 number in it with all of the
other exisiting Db fields. It is how I want the Existing Database Table
(above) to contain once it is updated.

Other table and/or the desired out put of the Existing Database Table (above)

LocId Facility CustId Seq1 Zone
1AA16A ABC 1234 11001 ZN54
2BB18B ABC 1234 12789 ZN67
4DD27A ABC 1234 10899 ZN67

*** All numbers in Seq1 are unique.

So this other table has fields LocID, Facility, CustID and Zone? If
so, why are you storing the same data redundantly in two tables!?

You should be able to create a Query joining the two tables on all
four of these fields: LocId to LocId, Facility to Facility, etc.
Change the query to an Update query and update [ExistingTable].[Seq1]
to [AnotherTable].[Seq1].

You may need to create a unique Index on the combination of the four
fields.

John W. Vinson JVinson *at* Wysard Of Info *dot* com

 

[Guest]

Hi Mr. Vinson

I appreciate your help and am sorry if I am confusing the issue.

Basically I created random (unique) sequence numbers for each record in the
existing table(A); which are contained in the other table(B).

What I am trying to accomplish is to update the SEQ1 field for each existing
record from the current value of (null) to the value of each record in the
other table.

 

[Guest]

Mr. Dewey, I appreciate your help, I just couldn't get this to work for me
and I don't want to mess up the Db so I will wait to see if I can figure
something else out.

Thank you for your time.

Backup your database and then use this query to update your Table A from data
in Table B. Substitute your actual table names.
UPDATE [Table A] INNER JOIN [Table B] ON ([Table A].Zone = [Table B].Zone)
AND ([Table A].CustId = [Table B].CustId) AND ([Table A].Facility = [Table
B].Facility) AND ([Table A].LocId = [Table B].LocId) SET [Table A].Seq1 =
[Table B].[Seq1]
WHERE ((([Table A].Seq1) Is Null));


--
KARL DEWEY
Build a little - Test a little

Hi!

Existing Database Table (ODBC connection) is:

LocId Facility CustId Seq1 Zone
1AA16A ABC 1234 (null) ZN54
2BB18B ABC 1234 (null) ZN67
4DD27A ABC 1234 (null) ZN67

=== I have another table that has the Seq1 number in it with all of the
other exisiting Db fields. It is how I want the Existing Database Table
(above) to contain once it is updated.

Other table and/or the desired out put of the Existing Database Table (above)

LocId Facility CustId Seq1 Zone
1AA16A ABC 1234 11001 ZN54
2BB18B ABC 1234 12789 ZN67
4DD27A ABC 1234 10899 ZN67

*** All numbers in Seq1 are unique.

Thanks!

 

[John W. Vinson]

Hi Mr. Vinson

I appreciate your help and am sorry if I am confusing the issue.

Basically I created random (unique) sequence numbers for each record in the
existing table(A); which are contained in the other table(B).

What I am trying to accomplish is to update the SEQ1 field for each existing
record from the current value of (null) to the value of each record in the
other table.

That is what the query that Karl and I both suggested will do.

You say in response to Karl that "it didn't work". That gives us very
little to go on! How did it "not work"? Could you post the SQL view of
the query, and indicate what happened when you tried to run it?

John W. Vinson [MVP]