Search code examples
mysqlnavicat

update fields in MySQL based on condition

I have two tables, one accounts( login, password, coin, hwid) and one block_list(id, hwid). I want to check if accounts.hwid match with block_list.hwid, if yes then set on each account.hwid + 500 coin.

I use the above query for this :

UPDATE accounts SET coin=500  WHERE hwid IN (SELECT hwid FROM block_list)

This query works, but there are even 6 accounts with same hwid, i want to set +500 coins only on one account with hwid in block_list.

How i can limit updating of coins to one account with same hwid as in block_list?

So, i have 10 accounts with same hwid. I want to compare account.hwid with block_list.hwid and update just one account from account.sql with same hwid.

Example : In account tabel exist 10 accounts named admin and hwid 102012 In block_list exist only one hwid 102012

This query :

UPDATE accounts SET coin=500  WHERE hwid IN (SELECT hwid FROM block_list)

Will update all 10 accounts from accounts

I want to update just one account, I want to set coins + 500 more just on one account.. other 9 shoule be ignore..

Tried all ways, some help?

Solution

  • I am still not sure what is your problem, But syntatically, this should work.

    UPDATE accounts SET coin = coin+500  WHERE hwid = (SELECT hwid FROM block_list LIMIT 1)