Modifying two tables at once (with update & insert into)

Databases
1

Trying to see if I can combine these two queries, or if I have to it via PHP:

UPDATE users SET account_status = 1, token = NULL WHERE id_user = 5

INSERT INTO old_tokens (token) VALUES (:token)

What I want to do is insert the contents of the token field from the users table into the token field of the old_tokens table, and then set the token field in the users table to NULL. I can do this via php easily, but it is long series of loops and individual queries.

2

MySQL doesn’t support multiple insertions in one query.

You can use transactions for this, however.

3

actually it does

INSERT INTO mytable VALUES
 ( 1 , 'foo' )
,( 2 , 'bar' )
,( 3 , 'qux' )
,( 4 , 'fap' )

but ShinVe isn’t trying to do multiple inserts, but rather, an update and an insert

ShinVe, what columns do those two tables have? does old_tokens really have only one column?

and why do you say it would require a “long series of loops and individual queries” to do this in php?

4

My bad, I didn’t phrase that correctly. Meant that it does not support multiple queries (well, it does if you count transactions)

5

old_tokens really only has one column, because I moved this out of the users table so that the users table (which is used on virtually every website load) is not bearing any additional query load that isn’t really necessary. I don’t know if that is great db design, but it lowered queries on this table, so I thought it would be beneficial instead of having a monolithic users table.

The reason I said that it would require a “long series of loops and individual queries” is because the query above was fairly simplified. I am actually using a WHERE clause that dynamically selects a range of user_id 's (like a BETWEEN clause) and it varies from scenario to scenario. (In an actual query, I will usually be selecting all users who were created within a certain time range). I simplified it here, but I can now see that that may have made it not clear on why I want to do this with one query. In PHP I can put the range of the WHERE clause into an array, and look through each element of the array, run the query mentioned (so user_id is updated, and then the old token, which is remembered via php, is then put into another sql query where it is inserted into old_tokens.) So if I do this for 10000 users, that is 2 db queries per user, or 20k db queries. I was hoping to get this down to 1 query per user, or even better, 1 query overall, if possible.