 | |
#1
 
| |||||
| |||||
|
Update with Join Query -
10-04-2010
, 04:44 PM
|
Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ EmployeeID | int(11) | NO | | NULL | | Address | varchar(45) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ |
mysql> DESCRIBE Data;
+------------+-------------+------+-----+---------+-------+
Quote:
|
Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ EmployeeID | int(11) | NO | | NULL | | Address | varchar(45) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ |
mysql> select * from Employees;
+------------+---------+
Quote:
|
EmployeeID | Address | +------------+---------+ 1 | NULL | 2 | NULL | 3 | NULL | +------------+---------+ |
mysql> select * from Data; # the real Data table has more fields
+------------+---------+
Quote:
|
EmployeeID | Address | +------------+---------+ 1 | A1 | 1 | A1 | 1 | A1 | 2 | A2 | 2 | A2 | +------------+---------+ |
mysql> UPDATE Employees JOIN Data ON Employees.EmployeeID =
Data.EmployeeID SET Employees.Address = Data.Address;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from Employees;
+------------+---------+
Quote:
|
EmployeeID | Address | +------------+---------+ 1 | A1 | 2 | A2 | 3 | NULL | +------------+---------+ |
#2
| |||
| |||
|
|
Hi, I need help with a JOIN Query. *My DB has two tables, Employees and Data. The table "Data" has duplicate rows. *I know this is a terrible design,I have no control over it. *It comes from an Xcel spreadsheet converted to a MySQL table. I want to JOIN the two tables so I can fill in the Address field of the Employees table. Because there are multiple rows in Data with the same Address field, the Address field of Employees is updated multiple times with the same value. Is there a better JOIN Query that will do the same thing? Thank you, Joe mysql> DESCRIBE Employees; +------------+-------------+------+-----+---------+-------+ | Field * * *| Type * * * *| Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | EmployeeID | int(11) * * | NO * | * * | NULL * *| * ** | | Address * *| varchar(45) | YES *| * * | NULL * *| * ** | +------------+-------------+------+-----+---------+-------+ mysql> DESCRIBE Data; +------------+-------------+------+-----+---------+-------+ | Field * * *| Type * * * *| Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | EmployeeID | int(11) * * | NO * | * * | NULL * *| * ** | | Address * *| varchar(45) | YES *| * * | NULL * *| * ** | +------------+-------------+------+-----+---------+-------+ mysql> select * from Employees; +------------+---------+ | EmployeeID | Address | +------------+---------+ | * * * * *1 | NULL * *| | * * * * *2 | NULL * *| | * * * * *3 | NULL * *| +------------+---------+ mysql> select * from Data; *# the real Data table has more fields +------------+---------+ | EmployeeID | Address | +------------+---------+ | * * * * *1 | A1 * * *| | * * * * *1 | A1 * * *| | * * * * *1 | A1 * * *| | * * * * *2 | A2 * * *| | * * * * *2 | A2 * * *| +------------+---------+ mysql> UPDATE Employees JOIN Data ON Employees.EmployeeID = Data.EmployeeID SET Employees.Address = Data.Address; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 *Changed: 2 *Warnings: 0 mysql> select * from Employees; +------------+---------+ | EmployeeID | Address | +------------+---------+ | * * * * *1 | A1 * * *| | * * * * *2 | A2 * * *| | * * * * *3 | NULL * *| +------------+---------+ |
data initially. That way you will only have one record for each
employee.
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.