0

I have 2 tables, ranks and accounts. When inserting into accounts I fetch the ID of the rank first, storing it in a variable on the script running the queries, then insert the new account into the table with the rank ID. How could I combine this into a single MySQL statement?

UPDATE:

// This is pseudo code.
var acct = GetAcct()
var a = MySQL.query("SELECT `id` FROM `ranks`
           WHERE `name`=" + acct.rankName).firstResult()
var b = MySQL.query("INSERT INTO `accounts` (`sid`, `did`,`rank`)
           VALUES (" + acct.sid + "," + acct.did + "," + a + ")
           ON DUPLICATE KEY UPDATE
               `did`=" + acct.did + ",`sid`=" + acct.sid + ",
               `rank`=" + a)
  • 1
    Please edit the question and add the code that you use now. – ypercubeᵀᴹ Jun 3 '18 at 15:41
  • You likely can put the query to fetch the rank's id as a subquery into the VALUES clause or into the list of selected columns, if you use an INSERT ... SELECT .... Without you posting your table's structure and the queries in use now a more specific answer isn't possible. – sticky bit Jun 3 '18 at 16:27
1

Use INSERT .. SELECT instead of INSERT .. VALUES:

INSERT INTO accounts (name       , login       , ..., rank        , ...)
SELECT                'some name', 'some login', ..., ranks.rankID, ...
FROM ranks
WHERE ranks.name = 'some rank' /* ORDER BY ranks.somefield LIMIT 1 */

The values You need to insert directly (name, login, etc.) are added into SELECT part literally, the data obtained from ranks table is selected by common way. If there exists more than 1 record in ranks appropriate to criteria You use - add ordering and limiting to select (and add into destination table) one record only.

  • Sorry I'm having trouble reading your SQL statement. I posted pseudo-code of how I currently do it, if it helps. Do you mind reformatting your statement a little? As for the ranks table, it has 2 fields: one primary (id) and one unique (name). – Timberwolf Jun 4 '18 at 16:28
0
INSERT INTO accounts (sid, did, rank)
    SELECT a.sid, a.did, r.id
        FROM acct AS a
        JOIN ranks AS r  ON a.??? = r.???
    ON DUPLICATE KEY UPDATE
        rank = r.id

This assumes that accounts has a unique key of (sid, did).

To discuss this further, we desperately need SHOW CREATE TABLE for the relevant tables.

  • I was thinking of posting something along these lines, except I thought a.sid, a.did and a.??? would need to be parameters, so the SELECT list would look something like SELECT ?, ?, r.id and the joining condition like r.name = ?. Not sure about the ON DUPLICATE clause, though. – Andriy M Jun 6 '18 at 6:29
  • @AndriyM - Your Question, and your Comment here, are quite vague. Please tighten up your explanation of the problem. Perhaps also provide some sample data. – Rick James Jun 6 '18 at 16:15
  • Hmm, I didn't ask any questions, only commented. Anyway, what I'm saying is, the values for sid and did are provided in the client code, they are not coming from a query. Only the rank value comes from a preceding query (the id from the ranks table). – Andriy M Jun 6 '18 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.