Tim Winders wrote:
>
> I am new to mySQL and can't figure out how (or even if) I can do this.
>
> I have two tables (simplified) like this:
>
> Jobs:
> +-------------------+-----------------------+------+-----+------------+-------+
> | Field | Type | Null | Key | Default |
> Extra |
> +-------------------+-----------------------+------+-----+------------+-------+
> | JobID | char(10) | | PRI |
> | |
> | EmployerID | int(11) | | MUL | 0
> | |
> +-------------------+-----------------------+------+-----+------------+-------+
>
> Employer:
> +------------+---------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+---------------+------+-----+---------+----------------+
> | Employer | char(50) | | | | |
> | EmployerID | int(11) | | PRI | 0 | auto_increment |
> +------------+---------------+------+-----+---------+----------------+
>
> Right now, the data is pretty scrambled. There are many Employer
> entries with are (esentially) duplicates. There less than 500 records
> in each table, so I can quickly look at the Employer table and figure
> out which Employer should be correct, then I want to write an SQL
> statement to update the Jobs table so all the EmployerIDs get changed to
> the correct one for each similar Employer. Something like this: (which
> of course does not work)
>
> update Jobs set Jobs.EmployerID=403 where Employer.Employer like %cox%;
>
> Can somebody help me to get this "correct" or tell me that it can't be
> done. Thanks.
>
> --
> === Tim
Read the manual on REPLACE INTO ... SELECT .. and possibly also ALTER
TABLE, if that does not help, let us know.
--
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
