如何在union查询后更新?

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

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

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