I have one table with City Name Table (citytable)
我有一张带有城市名表(citytable)的桌子
idcity | cityname | statename | codenumber
1 | Los Angeles | state2 | ...
2 | New York | state3 | ...
3 | New Jersey | state3 | ...
Code Number City Table (codetable)
代号城市表(代码表)
id | city | codenumber
1 | angeles | 031
2 | york | 064
3 | jersey | 075
How to SET or INSERT or UPDATE data 'codenumber' FROM 'codetable' fields INTO 'codenumber' column FROM citytable WHERE 'city' FROM codetable LIKE '%cityname%' FROM 'citytable'
? Thanks for your help advance.
如何设置或插入或更新数据'codenumber'从'codetable'字段INTO'codenumber'列FROM citytable WHERE'city'FROM codetable LIKE'%cityname%'FROM'citytable'?感谢您的帮助。
2 个解决方案
#1
2
use UPDATE with join
使用UPDATE加入
UPDATE cityTable a
INNER JOIN codeTable b
ON a.ID = b.ID
SET a.codeNumber = b.codeNumber
but I suspect here that ID
are AUTO_INCREMENT
ed column, if so,
但我怀疑ID是AUTO_INCREMENTed列,如果是这样,
UPDATE cityTable a
INNER JOIN codeTable b
ON a.cityName LIKE CONCAT('%', b.city,'%')
SET a.codeNumber = b.codeNumber
#2
0
@JW and I arrived at the following more or less simultaneously, but JW had the edge on time!
@JW和我或多或少同时到达了以下,但JW有时间优势!
UPDATE citytable a
INNER JOIN codetable b ON a.cityname LIKE CONCAT('%',b.city,'%')
SET a.codenumber = b.codenumber
Update after Morgan asked for a php example of doing this in a SELECT followed by loop with UPDATE.
在Morgan要求在SELECT中执行此操作的php示例之后更新,然后使用UPDATE进行循环。
<?php
#Fill out the four variables below.
#
#This is just an example!
#If you are going to use this for real, you want to put the top
#4 variables in a separate file and include that file into this
#file via phps include directive. That separate file needs
#to be in a tightly security controlled directory, because
#your database password is in the file.
#
#For security reasons, the variables below must not come from
#user supplied data (from a POST or GET or the SESSION variables).
#
$dbName = '';
$hostName = '';
$username = '';
$password = '';
$dbh = new PDO("mysql:dbname=$dbName;host=$hostName",
$username, $password);
$dbh->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION);
$sqlSelect = "
SELECT cityname, codenumber
FROM city a
INNER JOIN codetable b ON
a.cityname LIKE CONCAT('%',b.city,'%');";
$sqlUpdate = "
UPDATE citytable SET codenumber = ? WHERE cityname = ?";
$rows = $dbh->query($sqlSelect)->fetchAll();
$sth = $dbh->prepare($sqlUpdate);
foreach($rows as $row) {
$codeNumber = $row['codenumber'];
$cityName = $row['cityname'];
$sth->execute(array($codeNumber,$cityName));
}
#1
2
use UPDATE with join
使用UPDATE加入
UPDATE cityTable a
INNER JOIN codeTable b
ON a.ID = b.ID
SET a.codeNumber = b.codeNumber
but I suspect here that ID
are AUTO_INCREMENT
ed column, if so,
但我怀疑ID是AUTO_INCREMENTed列,如果是这样,
UPDATE cityTable a
INNER JOIN codeTable b
ON a.cityName LIKE CONCAT('%', b.city,'%')
SET a.codeNumber = b.codeNumber
#2
0
@JW and I arrived at the following more or less simultaneously, but JW had the edge on time!
@JW和我或多或少同时到达了以下,但JW有时间优势!
UPDATE citytable a
INNER JOIN codetable b ON a.cityname LIKE CONCAT('%',b.city,'%')
SET a.codenumber = b.codenumber
Update after Morgan asked for a php example of doing this in a SELECT followed by loop with UPDATE.
在Morgan要求在SELECT中执行此操作的php示例之后更新,然后使用UPDATE进行循环。
<?php
#Fill out the four variables below.
#
#This is just an example!
#If you are going to use this for real, you want to put the top
#4 variables in a separate file and include that file into this
#file via phps include directive. That separate file needs
#to be in a tightly security controlled directory, because
#your database password is in the file.
#
#For security reasons, the variables below must not come from
#user supplied data (from a POST or GET or the SESSION variables).
#
$dbName = '';
$hostName = '';
$username = '';
$password = '';
$dbh = new PDO("mysql:dbname=$dbName;host=$hostName",
$username, $password);
$dbh->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION);
$sqlSelect = "
SELECT cityname, codenumber
FROM city a
INNER JOIN codetable b ON
a.cityname LIKE CONCAT('%',b.city,'%');";
$sqlUpdate = "
UPDATE citytable SET codenumber = ? WHERE cityname = ?";
$rows = $dbh->query($sqlSelect)->fetchAll();
$sth = $dbh->prepare($sqlUpdate);
foreach($rows as $row) {
$codeNumber = $row['codenumber'];
$cityName = $row['cityname'];
$sth->execute(array($codeNumber,$cityName));
}