Roger Fernandez Guri Roger Fernandez Guri - 5 months ago 7
SQL Question

LIMIT with SELECT COUNT query in SQL

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

Answer

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