?
Solved

Limit has variable limits workaround needed?

Posted on 2008-06-20
2
Medium Priority
?
284 Views
Last Modified: 2011-07-08
Seem like I cannot use a variable in a limit clause like so: Limit @rowcount

Is there any workaround for the following syntax?

select @row_count = (Select colRows from tbltemp where colName = 'table1');
set @adj_row_count = (@row_count *.5);

INSERT INTO table2 (col1, col2) Select col1, col2 FROM table1 Limit @adj_row_count

0
Comment
Question by:kvnsdr
2 Comments
 
LVL 9

Accepted Solution

by:
Rurne earned 2000 total points
ID: 21833920
You're going to need to create a user function to replicate this.  @variables are not allowed in straightforward SQL in MySQL. See:
http://bugs.mysql.com/bug.php?id=11918
http://bugs.mysql.com/bug.php?id=8094

The alternative is to create a stored procedure and call it instead (e.g., "CALL variable_insert();").  Try:
CREATE PROCEDURE variable_insert()
BEGIN
DECLARE row_count, adj_row_count INT;
SELECT colRows INTO row_count FROM tbltemp WHERE colName = 'table1';
set @adj_row_count = row_count / 2;
 
INSERT INTO table2 (col1, col2) SELECT col1, col2 FROM table1 LIMIT adj_row_count;
END;

Open in new window

0
 
LVL 1

Expert Comment

by:CMDAI
ID: 36151068
Im posting this for people like me who spent ages looking for an easy workaround for limit @variable
# THIS WILL FAIL
select * from some_table limit @a ;

#Instead use this work around
@a=0;
select * from some_table where (@a := @a +1) < @your_limit;

Open in new window

0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question