• 0

MySQL Statement Help


Question

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.

Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0

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
  • Like 1
Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.