locked
How to use SELECT COUNT inside INSERT command for MySQL Database RRS feed

  • Question

  • User-830711696 posted

    Hi i'm currently trying to insert some info into my DB and i'm trying to do it with just one simple sql but it's not working.

    I got a insert and in there i got a orderr where i want to set it to the last orderr + 1 so here's the code i'm trying with:

    string hemtinfo = "INSERT INTO gallery(ref_name,bild,bild_namn,orderr) VALUES('" + bildmeny.SelectedValue.ToString() + "','" + xFilePath.Value + "','" + bildnamn.Text + "',(SELECT COUNT(orderr) FROM gallery WHERE gallery.ref_name='" + bildmeny.SelectedValue.ToString() + "') + 1)";

    Any ideas how to do this?

    i get this error from using this code :

    System.Data.Odbc.OdbcException: ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.61-0ubuntu0.10.04.1-log]You can't specify target table 'gallery' for update in FROM clause



    Friday, November 9, 2012 2:38 AM

Answers

  • User418155246 posted

    Sorry, not checked my emails for a day or two. Try this;

    INSERT INTO gallery (ref_name,bild,bild_namn,orderr) 
    VALUES('" + bildmeny.SelectedValue.ToString() + "',
           '" + xFilePath.Value + "',
           '" + bildnamn.Text + "',
            (SELECT COUNT(orderr) FROM (SELECT * FROM gallery) AS g WHERE g.ref_name='" + bildmeny.SelectedValue.ToString() + "') + 1)";

    EDIT: Apologies for the misunderstanding on my previous post, i have been doing alot of ecommerce work lateley so when i see "order" automatically assume itsan order for a product or service rather than "sort_order".

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 13, 2012 4:36 AM

All replies

  • User418155246 posted

    Just a quick question, looking at your SQL, why aren't you using the built in Auto-Increment feature of your database to increment the orderr column by 1 rather than trying to manually increment it yourself. If you start deleting orders from your table you are going to start reusing order numbers that have been used and will run into all sort of problems.

    If you disagree with the above let me know and i will be happy to try and help you fix your statement, but i advise you turn the orderr column to an autoincrementing column that you dont have to touch with your SQL statements.

    Friday, November 9, 2012 4:48 AM
  • User-830711696 posted

    Just a quick question, looking at your SQL, why aren't you using the built in Auto-Increment feature of your database to increment the orderr column by 1 rather than trying to manually increment it yourself. If you start deleting orders from your table you are going to start reusing order numbers that have been used and will run into all sort of problems.

    If you disagree with the above let me know and i will be happy to try and help you fix your statement, but i advise you turn the orderr column to an autoincrementing column that you dont have to touch with your SQL statements.

    in this case i disagree to the above since i need to keep it that way when they start moving the order around.
    Or well i dont really need it to be so but i prefer it that way, Anyway i actually tried with a Auto-Increment but it didnt work since i
    already have one for the ID in the table and it says i can't have two :o

    i know what the problem is now with this statement, it doesnt work since i try to use the information in select from the same table i'm using to insert in and i can't use the same table since it gets a temprorary table or something like that :o wich doesnt work in mySql but in mssql , so i just picked it out first with a reader and then did another query , so now i'm simply executing two querys instead of one since it's not that much of a problem but i would prefer to only have to execute info from/to the database once =)

    so if you have a solution for MySql i'd gladly try it :D

    Friday, November 9, 2012 4:56 AM
  • User637893553 posted

    Hi,

    Looks like circular reference issue, try this query

    string hemtinfo ="INSERT INTO gallery(ref_name,bild,bild_namn,orderr) values(
    select `" + bildmeny.SelectedValue.ToString() + "`,`" + xFilePath.Value + "`,`" + bildnamn.Text + "`,count(orderr) +1 FROM gallery where gallery.ref_name='" +  bildmeny.SelectedValue.ToString() + "')";

     i didn't compiled above query, might be need to add some group clause etc., u just test and let us know.

    Monday, November 12, 2012 8:08 AM
  • User-1407477457 posted

    Hi,

    Looks like circular reference issue, try this query

    string hemtinfo ="INSERT INTO gallery(ref_name,bild,bild_namn,orderr) values( 
    select `" + bildmeny.SelectedValue.ToString() + "`,`" + xFilePath.Value + "`,`" + bildnamn.Text + "`,count(orderr) +1 FROM gallery where gallery.ref_name='" +  bildmeny.SelectedValue.ToString() + "')";

     i didn't compiled above query, might be need to add some group clause etc., u just test and let us know.


    Looks like that one will crash.  You have to use a subquery to get the count. 

    I agree with the first responder who suggested letting the database generate the number.  This method will be slow.  Also, if orderr is the primary key and records get deleted, there will be a problem.

    Monday, November 12, 2012 8:42 AM
  • User-830711696 posted
    Orderr is not My primary key, it is the order of the list so they can change it at will
    Monday, November 12, 2012 5:59 PM
  • User418155246 posted

    Sorry, not checked my emails for a day or two. Try this;

    INSERT INTO gallery (ref_name,bild,bild_namn,orderr) 
    VALUES('" + bildmeny.SelectedValue.ToString() + "',
           '" + xFilePath.Value + "',
           '" + bildnamn.Text + "',
            (SELECT COUNT(orderr) FROM (SELECT * FROM gallery) AS g WHERE g.ref_name='" + bildmeny.SelectedValue.ToString() + "') + 1)";

    EDIT: Apologies for the misunderstanding on my previous post, i have been doing alot of ecommerce work lateley so when i see "order" automatically assume itsan order for a product or service rather than "sort_order".

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 13, 2012 4:36 AM
  • User-830711696 posted

    Sorry, not checked my emails for a day or two. Try this;

    INSERT INTO gallery (ref_name,bild,bild_namn,orderr)
    VALUES('" + bildmeny.SelectedValue.ToString() + "',
           '" + xFilePath.Value + "',
           '" + bildnamn.Text + "',
            (SELECT COUNT(orderr) FROM (SELECT * FROM gallery) AS g WHERE g.ref_name='" + bildmeny.SelectedValue.ToString() + "') + 1)";

    EDIT: Apologies for the misunderstanding on my previous post, i have been doing alot of ecommerce work lateley so when i see "order" automatically assume itsan order for a product or service rather than "sort_order".

    I don't mind at all =) i appreciate all comments i get =) and this worked perfectly thank you alot :D

    Thursday, November 15, 2012 2:27 AM