Replace First Occurrence of a String (1 Viewer)

N

Natic86

New member

Local time

Today, 02:15

Joined

Jan 12, 2015

Messages

2

• Jan 12, 2015

• #1

Here are the 2 tables that I am working with:

Account

Account
0041980041
0051670051
0052330052
0053490053


Conversion

Account Conversion
0041 10000151
0051 10000152
0052 10000153
0053 100001524

I need to update the Account table using the Conversion table by replacing the first 4 digits in the Account table with the Conversion value. For example, the first record should be updated from 0041980041 to 10000151980041.

Thanks!

 

pr2-eugin

Super Moderator

Local time

Today, 09:15

Joined

Nov 30, 2011

Messages

8,494

• Jan 12, 2015

• #2

What is the relationship between the two tables?

 

N

Natic86

New member

Local time

Today, 02:15

Joined

Jan 12, 2015

Messages

2

• Jan 12, 2015

• #3

Hello! Thank you for responding!!!!

The relationship is many to many. There are approximately 11,000 records in the account table, and all the records begin with one of the four records in the Account column found in the Conversion table.

Please let me know if you need any further information!

 

P

plog

Banishment Pending

Local time

Today, 03:15

Joined

May 11, 2011

Messages

11,638

• Jan 12, 2015

• #4

You really should store the data like your second table does--with 2 columns for the discrete pieces of the data.

That's how I would do this task:

1. Make a backup of your database.
2. Make a new field in your table called Conversion.
3. Move all the characters after the first 4 in Account to the Conversion field.
4. Update the Account field to just the first 4 characters currently in it.
5. Create an UPDATE query using your two tables, linked via their Account field.

 

jdraw

Super Moderator

Staff member

Local time

Today, 04:15

Joined

Jan 23, 2006

Messages

15,379

• Jan 12, 2015

• #5

Please tell us in detail plain English about the Many to Many.

Also, what is the business rationale for this conversion/change?----Just curious.