Sign in to follow this  
Followers 0

MySQL Statement Help

7 posts in this topic

Posted

Let's pretend the MySQL table is like so:

 

users:

id (auto increment primary key)

name (varchar[255])

inserted (datetime)

 

Basically I want to do a check to see if the table 'users' has less than 20 rows in it, and if so insert a new row.

 

I'm hoping to do this with one MySQL command.

Share this post


Link to post
Share on other sites

Posted

you may be able to use

INSERT INTO TargetTable(Column1, Column2, ...)
    SELECT 'Value For Column 1', 'Value For Column 2', ...
      FROM Dual
     WHERE (SELECT COUNT(*) FROM TargetTable WHERE ...Some Condition...) < 20;

what Language are you using?

 

to get the number of rows you can use

SELECT COUNT(*) FROM TABLE_NAME;

It will be slow on large tables

 

Then your the lgauage your using to do the rest  so in normal 

SELECT COUNT(*) FROM TABLE_NAME

if count is below 20 then

add new row to table

else

do ntohing

End if
1 person likes this

Share this post


Link to post
Share on other sites

Posted

it may be more efficient to do SELECT COUNT(0) FROM TABLE;

 

I know you can do that on MSSQL but maybe MSSQL/MYSQL optimizes it in the background, knowing you don't care about the columns in a COUNT() function.

 

Either way, Haggis is right, checking count on the table is the way to go.

Share this post


Link to post
Share on other sites

Posted

Hey Haggis,

 

Thanks for your reply. I'm building this for a PHP website that's using MySQL.

 

The table won't be large, it'll only hold 50-100 records, it's for a contest. The reason I want to make it all in one MySQL command is so that if two users try to do it at the same time, only one will be successful. Instead of both doing a MySQL Count(*) and seeing it's below a number (let's say 20), and then both doing an INSERT, when only one should be able to.

 

I need to limit how many records are in this table, as it's for the prizes, and I just wanted to safeguard against it.  Currently I prepopulate the 50-100 rows in the table, and run an UPDATE command, but was hoping to make this more dynamic, as I know there has to be a better way.

 

So basically I was hoping I could have something like:

 

INSERT INTO targetTable (key, key, key) VALUES (value, value, value) WHERE (SELECT count(*) FROM targetTable) < 20

 

I tried that as an example, it didn't work though.

Share this post


Link to post
Share on other sites

Posted

Try an if statement in the SQL. This is MSSQL:

 

if (select COUNT(*) from MyTable) < 20

   begin

         insert into TableX......

   end

 

Share this post


Link to post
Share on other sites

Posted

Thanks for the suggestions guys... seems MySQL doesn't let you do an IF statement in a regular query, only in stored functions, so no luck.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.