Help Query SQL

[stalker66]

Hi guys. 

 

I have just one question regarding core side and using the SQL statements to update/insert datas into the database.

How could i implement the following query when a new character is created?

 

("UPDATE characters SET botprev_fail_account = (SELECT botprev_fail_account FROM accounts WHERE account_name=login")

 

I want that the new character that's created on the same account to have the botprev_fail_account value taken from the ACCOUNTS table and inserted into botprev_fail_account from CHARACTERS table.

The account_name and login have the same value.

 

 

The following code that i tried doesn't work:

 

			try (Connection con2 = DatabaseFactory.getConnection())
			{
				PreparedStatement statement2 = con2.prepareStatement("UPDATE characters SET botprev_fail_account = (SELECT botprev_fail_account FROM accounts WHERE account_name=?");

				statement2.setString(1, _accountName);
				statement2.executeUpdate();
				statement2.close();
			}

 

 

 

[melron]

if in table characters the field name is 'account_name'

and in table accounts the field name is 'login':

 

UPDATE characters c, accounts a SET c.botprev_fail_account = a.botprev_fail_account WHERE c.account_name = ? and c.account_name = a.login;

 

Else, edit the query and set the right field names

Edited October 31, 2019 by melron

[Mantosh]

Your SQL is missing closing parenthesis.

UPDATE characters SET botprev_fail_account = (SELECT botprev_fail_account FROM accounts WHERE account_name = ?)

Note, that this will update ALL characters botprev_fail_account to the value of botprev_fail_account pulled from that one account. As far as I understand that's not what You want. To fix that You should add a WHERE for characters part of the query. Something like this:

UPDATE characters SET botprev_fail_account = (SELECT botprev_fail_account FROM accounts WHERE account_name = ?) WHERE uID = ?

 

Also, instead of SELECT You could do it through JOIN like so:

UPDATE characters c INNER JOIN accounts a ON c.accountID = a.uID SET c.botprev_fail_account = a.botprev_fail_account WHERE c.uID = ?

Alternatively, just to give You some options, You could fetch botprev_fail_account value from accounts instead of saving (I assume) same value on two tables.

SELECT a.botprev_fail_account, c.something FROM characters c INNER JOIN accounts a ON c.accountID = a.uID WHERE c.uID = ?

 

Please note, that I haven't checked if any of those work and I assumed few column names - change them per Your database as needed.

[stalker66]

Thanks guys! It worked. I used the solution from @melron. Thanks a bunch!