A complex problem for a newbie... 2

[Germaris]

I am a real beginner and I have a complex problem to solve.

First, I briefly explain the basics:
I manage the Directory of an Association of Former Students of French Military Prep Schools.
In my Database, each school has its own Table.
Inside each Table, a row contains no less than sixteen fields of information.
Two of them are 'postalCode' and 'region'.

France is divided into administrative zones called "department(s)" which are numbered with two digit numbers.
Departments located in the same geographical zone are grouped into "region(s)"

Town's Postal Codes are five digit numbers.
The first two digits indicate the department (see above) and the three other digits the town. I have allocated to each region a two digit code.

So, for example the Town 87530 is located in the Department #87 and has the number 530.
The department #87 is in the Region #14.
Also, the Town 19854 is located in the Department #19 and has the number 854.
The department #19 is also in the Region #14.

And now, the problem itself.
I want to tell this to my Table : Wherever you encounter in 'postalCode' Field rows BEGINNING with 87 or 19, insert the Value 14 in the 'region' Field.

I have tried to be as clear as possible. Feel free to ask any needed info...

And many thanks in advance for your help!

Contact: gm[at]germaris.com
My motto: "Simple is Best

 

[duncdude]

I'm a real beginner also - I started learning MySQL yesterday

I would firstly write an SQL statement that would find all my records that satisfy the criteria

SELECT *
FROM database
WHERE postalCode LIKE '%89' || postalCode LIKE '%19';

then I would expand that non-destructive search - if it reveals only the records you are interested in - to include an INSERT INTO

Sorry I can't be much more help than that


Kind Regards
Duncan

 

[Germaris]

Welcome to the Newbie's Club !!! ;-)
Yes, I think it is a good start, but hope somebody will help us to continue...
Thanks!

A+

Contact: gm[at]germaris.com
My motto: "Simple is Best

 

[KarveR]

update yourtable set region = '14' where substring(postalCode,0,2) in ('19','89');

*note the substring may need to be substring(blah,1,2)

eg substring(fieldname,start_postion,length);

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Germaris]

Hi KarveR !

THIS IS WONDERFUL, IT WORKS PERFECTLY !!!!
Many thanks you save me a lot of time as there is more than 2,000 entries to update !!!
I awarded you a Star.
Have a very nice and sunny day!

Best regards.

Contact: gm[at]germaris.com
My motto: "Simple is Best

 

[KarveR]

Glad to help, have a great weekend!

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.