Solved

Limit has variable limits workaround needed?

Posted on 2008-06-20
2
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Accepted Solution

by:
Rurne earned 500 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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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