Error in inner join update sql

Question

I have 2 tables. In one table I have id and in another I have userId. This is the only connection between them.

Problem is: In one table I have username and in another I have his balance. I want to get user's balance according to his username. Since there is only one column which is connecting them and it is ID, I need to find a way how to update users balance if I have only his username.

Here is what I have tried but it is not working:

  UPDATE t1
  SET t1.balance = '999'
  FROM bitcoin.accountbalance AS t1
  INNER JOIN bitcoin.webusers AS t2
  ON t1.userId = t2.id
  WHERE t2.username = 'simpanz';

EDIT:

ERROR IS: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM bitcoin.accountbalance AS t1   INNER JOIN bitcoin.webusers AS t2     WHERE ' at line 3

I use MySQL.

Are you using MySQL or SQL Server? – Gordon Linoff May 26 '14 at 15:44 — Gordon Linoff, May 26 '14 at 15:44

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

Try this

 UPDATE  bitcoin.accountbalance AS t1

 INNER JOIN bitcoin.webusers AS t2
 ON t1.userId = t2.id
 SET t1.balance = '999'
 WHERE t2.username = 'simpanz';

you have to join tables and then SET what you want.

check this

edited May 23 '17 at 11:52

Community

1
1

answered May 26 '14 at 15:43

echo_Me

37,078
5
58
78

sorry it was mistake , try now. – echo_Me May 26 '14 at 15:49

score 1 · Accepted Answer · answered May 26 '14 at 15:46

1

This will work in both SQL Server and MySQL:

UPDATE accountbalance
  SET balance = '999'
  WHERE EXISTS (SELECT 1
                FROM webusers
                WHERE webusers.id = accountbalance.userId AND
                      webusers.username = 'simpanz'
               );

answered May 26 '14 at 15:46

Gordon Linoff

1,242,037
58
646
786

This shows this error: Error Code: 1054. Unknown column 't1.balance' in 'field list' – Ondrej Tokar May 26 '14 at 15:50
@OndrejTokar . . . That needs to go anyway. SQL Server doesn't like table aliases in `set` statements. – Gordon Linoff May 26 '14 at 15:51
This was the answer: UPDATE accountbalance SET balance = '999999' WHERE userId = (SELECT id FROM webusers WHERE username = 'simpanz' ); Since you were the closest cn you edit your answer so I can mark it as the right? Thank you. – Ondrej Tokar May 26 '14 at 16:04
@OndrejTokar . . . That answer should be equivalent to what is in the answer. What is the issue with the `exists` version? – Gordon Linoff May 27 '14 at 01:25

score 1 · Answer 3 · answered May 26 '14 at 15:50

1

If you are using MySQL try this and let me know if it works:

UPDATE bitcoin.accountbalance t1
INNER JOIN bitcoin.webusers t2
ON t1.userId = t2.id
SET t1.balance = '999'
WHERE t2.username = 'simpanz';

answered May 26 '14 at 15:50

iSaumya

1,503
5
21
50

Error in inner join update sql

3 Answers3