mysql query help - Printable Version

mysql query help - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: mysql query help (/showthread.php?tid=602512)

mysql query help - Richie© - 08.03.2016

Hi

A long time ago i made a mistake by handling house table by playername instead of userid.
Its working, but i want the house table to be dealt with by userid instead.

The problem is that there is alot of houses in the table already and i just added userid.houses field now.

Quote:

Table: houses
Fields: userid, username ..

Quote:

Table: useraccounts
Fields: userid, username ..

So is it possible to make a query that gets username.houses -> SELECT userid.useraccounts -> UPDATE userid.houses to add the correct value from userid.useraccounts to userid.houses ?

I know this can be done with pawn by looping houses -> SELECT userid FROM useraccounts WHERE username = 'HouseOwner' -> then send a new query to UPDATE userid in houses. But i want to know if there is a way this can be done in 1 single query in phpmyadmin.

Thanks in advance.

Re: mysql query help - zPain - 08.03.2016

Try this:

Код:

UPDATE houses INNER JOIN useraccounts ON houses.username = useraccounts.username SET userid = useraccounts.userid

Re: mysql query help - Vince - 08.03.2016

You're probably going to need to write a stored procedure with a loop in order to do this. Unfortunately it's been a long time for me too so I can't exactly remember how to do that.

Re: mysql query help - zPain - 08.03.2016

Quote:

Originally Posted by Vince

You're probably going to need to write a stored procedure with a loop in order to do this. Unfortunately it's been a long time for me too so I can't exactly remember how to do that.

http://www.mysqltutorial.org/mysql-update-join/

I wrote the query above based on this page's content and it worked just fine. I didn't know such thing was possible until now.

Quote:

Originally Posted by zPain

Try this:

Код:

UPDATE houses INNER JOIN useraccounts ON houses.username = useraccounts.username SET userid = useraccounts.userid

This is exactly what i wanted, thank you!