LIMIT with SELECT COUNT query in SQL

Question

I'm wanting to limit an INSERT with a number of rows of another table like this:

INSERT INTO pacte (alianca_en_pacte, tipus)
SELECT id_alianca_en_pacte1 ,tipus_pacte1
FROM c_alianca
LIMIT 0, SELECT COUNT(tipus_pacte1) FROM c_alianca WHERE tipus_pacte1 IS NOT NULL

The following query:

SELECT COUNT(tipus_pacte1) FROM c_alianca WHERE tipus_pacte1 IS NOT NULL

returns a 4

When running the script, the following error apears:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM c_alianca' at line 4

you can't use subquery on limit but also, you don't need it. make your limitation on your query. Check my answer. — Nesim Razon
– Nesim Razon, Commented Apr 23, 2012 at 10:50

Nesim Razon · Accepted Answer · 2012-04-23 10:48:13Z

5

Why not just query your criteria and insert those like this?

INSERT INTO pacte (alianca_en_pacte, tipus)
SELECT id_alianca_en_pacte1 ,tipus_pacte1
FROM c_alianca
WHERE tipus_pacte1 IS NOT NULL

answered Apr 23, 2012 at 10:48

Nesim Razon

9,8043 gold badges38 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eggyal Over a year ago

Duh. I was thinking too literally! +1

Roger Fernandez Guri Over a year ago

Thanks, it works perfectly! I think I was racking my brain when it was so simple..

Community · Accepted Answer · 2017-05-23 11:58:47Z

4

MySQL currently doesn't support having a subquery as an argument to LIMIT. You will need to use user variables across two queries:

SET @limit = (
    SELECT COUNT(tipus_pacte1) FROM c_alianca WHERE tipus_pacte1 IS NOT NULL
);

INSERT INTO pacte (alianca_en_pacte, tipus)
    SELECT id_alianca_en_pacte1 ,tipus_pacte1
    FROM c_alianca
    LIMIT 0, @limit;

But as suggested by Nesim Razon, in your example one can avoid using LIMIT entirely:

INSERT INTO pacte (alianca_en_pacte, tipus)
    SELECT id_alianca_en_pacte1 ,tipus_pacte1
    FROM c_alianca
    WHERE tipus_pacte1 IS NOT NULL;

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Apr 23, 2012 at 10:47

eggyal

127k18 gold badges220 silver badges244 bronze badges

Comments

scibuff · Accepted Answer · 2012-04-23 10:43:33Z

3

Is this what you're after

INSERT INTO pacte (alianca_en_pacte, tipus)
SELECT id_alianca_en_pacte1, tipus_pacte1, 
    ( SELECT COUNT(tipus_pacte1) FROM c_alianca WHERE tipus_pacte1 IS NOT NULL ) AS tipus 
FROM c_alianca LIMIT 0

answered Apr 23, 2012 at 10:43

scibuff

13.8k2 gold badges30 silver badges34 bronze badges

Comments

Em Aamir · Accepted Answer · 2016-06-16 08:27:43Z

0

below is the code for Limitation on ID....It is the example for you
Declare @Counter int 
Set @Counter=(SELECT Count(Student_ID) as 'StudentCount' FROM CourseSemOne 
where Student_ID=1 Having Count(Student_ID) < 6 and Count(Student_ID) > 0)
if(@Counter <6)
print'Sorry! You cannot add more than five subject data for a single student'
else
print'Insert Code';

edited Jun 16, 2016 at 8:27

answered Jun 16, 2016 at 8:00

Em Aamir

16 bronze badges

Collectives™ on Stack Overflow

LIMIT with SELECT COUNT query in SQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related