List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 8 1999 4:40pm
Subject:Re: Select with like from another table?
View as plain text  
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)
Thread
Select with like from another table?Tim Winders8 Jul
  • Re: Select with like from another table?T├Ánu Samuel8 Jul
  • Re: Select with like from another table?Sasha Pachev8 Jul