I have 2 table as seen below,
我有两个表,
coin
----
chanceNo
userID
coin_code
date
coin2
----
chanceNo
userID
coin_code
date
My query to find where '12345' is at which table, as seen below (which is correct and successful search & match),
我的查询找到了“12345”在哪个表中,如下所示(正确和成功的搜索与匹配),
SELECT coin_code from coin WHERE coin_code='12345' UNION
Select coin_code from coin2 WHERE coin_code='12345';
However, i am stuck with updating query after search. How can i update after i found coin_code='12345'.
但是,我一直在更新搜索后的查询。如何在找到coin_code='12345'后进行更新。
I know that the basic update query is like this,
我知道基本的更新查询是这样的,
UPDATE coin2 SET userID='name', date='12-12-12' WHERE coin_code='12345'
But what if the '12345' is in another table, coin? May i know, how to write this query?
但是如果‘12345’在另一张桌子上呢,硬币?请问这个查询怎么写?
2 个解决方案
#1
2
This query will tell you which table the coin_code was found in:
这个查询将告诉您coin_code出现在:
SELECT "coin" which_table, coin_code
FROM coin
WHERE coin_code = '12345'
UNION
SELECT "coin2" which_table, coin_code
FROM coin2
WHERE coin_code = '12345'
Now that you have the table name, you can substitute it into an UPDATE query:
现在有了表名,可以将其替换为UPDATE查询:
$row = mysqli_fetch_assoc($sel_stmt);
$upd_stmt = mysqli_prepare("UPDATE {$row[which_table]} SET userID = ?, date = ? WHERE coin_code = ?");
#2
1
Extending Barmar's answer you can do something like this,
扩展Barmar的答案你可以这样做,
$sql="SELECT 'coin' which_table, coin_code
FROM coin
WHERE coin_code = '12345'
UNION
SELECT 'coin2' which_table, coin_code
FROM coin2
WHERE coin_code = '12345'";
$result = mysqli_query($sql);
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
//Now updating the table
$sql_update = "UPDATE {$row['which_table']} SET userID='name', date='12-12-12'
WHERE coin_code='{$row['coin_code']}'";
mysqli_query($sql_update);
}
#1
2
This query will tell you which table the coin_code was found in:
这个查询将告诉您coin_code出现在:
SELECT "coin" which_table, coin_code
FROM coin
WHERE coin_code = '12345'
UNION
SELECT "coin2" which_table, coin_code
FROM coin2
WHERE coin_code = '12345'
Now that you have the table name, you can substitute it into an UPDATE query:
现在有了表名,可以将其替换为UPDATE查询:
$row = mysqli_fetch_assoc($sel_stmt);
$upd_stmt = mysqli_prepare("UPDATE {$row[which_table]} SET userID = ?, date = ? WHERE coin_code = ?");
#2
1
Extending Barmar's answer you can do something like this,
扩展Barmar的答案你可以这样做,
$sql="SELECT 'coin' which_table, coin_code
FROM coin
WHERE coin_code = '12345'
UNION
SELECT 'coin2' which_table, coin_code
FROM coin2
WHERE coin_code = '12345'";
$result = mysqli_query($sql);
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
//Now updating the table
$sql_update = "UPDATE {$row['which_table']} SET userID='name', date='12-12-12'
WHERE coin_code='{$row['coin_code']}'";
mysqli_query($sql_update);
}