Solved

Limit has variable limits workaround needed?

Posted on 2008-06-20
2
271 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now