Ошибка sql 1172 - Не ошибается лишь тот, кто ничего не делает!

The error is due to the INTO clause in the second SELECT statement. That wouldn’t be an error if the SELECT returned no more than one row.

But if that SELECT returns more than one row, MySQL will throw 1172 error. (You can test this by adding a «LIMIT 1» clause on that «SELECT ... INTO» statement, and verifying that the error is not thrown.

The procedure runs a SELECT to return a resultset, then issues an UPDATE statement to modify zero or more rows in a table.

The next part of the procedure is confusing; it’s not clear what we are trying to achieve. The SELECT can return zero, one or more rows. If the intent is to perform a conditional test to determine whether there are any rows with item_total > 0,

Then one possible «fix» (to avoid the 1172 error) would be to add

AND o.item_total > 0 LIMIT 1

to the «SELECT ... INTO» statement.

Without an «ORDER BY«, it’s indeterminate which row would be returned. But it doesn’t really look like it matters. Apart from the comparison of the returned o.item_total value, it doesn’t look like the procedure is doing anything else with the columns returned.

The following UPDATE statement is only going to be executed if an item_total>0 row was found.

And the UPDATE statement will update all rows that have an order_number greater than a specified constant.

There doesn’t appear to be any relationship to the row(s) returned by the previous SELECT ... INTO statement.

If the intent is to update the rows in order_status (that are related to a customer) if «any» row returned by the previous query has an item_total > 0, then the proposed «fix» would accomplish that.

The procedure then (conditionally) returns a second resultset (all rows from order_status with `order_stts = ‘waiting’), but only if there wasn’t any order_status row with and item_total>0.

We can fix the 1172 error, but this procedure has much bigger problems than that error. The whole procedure seems like bizarre approach to solving whatever problem it’s supposed to be solving. It just looks like a jumble of SQL statements kludged together without a clear design.

Источник

So, if try to run this query:

-- Make sure the variable is typed to a single value...
SET @my_id=300;
SELECT `my_id` INTO @my_id
FROM `my_table` WHERE `field_1`= 123 AND `field_2`IS NULL;

…then I get this error:

[Err] 1172 - Result consisted of more than one row

However, if I run this select:

SELECT COUNT(*)
FROM `my_table` WHERE `field_1`= 123 AND `field_2`IS NULL;

…then it returns ZERO (ie, no matches). How is zero more than one?

asked Sep 12, 2013 at 19:27

Paul MolodowitchPaul Molodowitch

1,3563 gold badges12 silver badges29 bronze badges

Whoops… realized what’s going on. The first SELECT INTO statement was being run from inside a procedure, and what it was ACTUALLY calling looked more like this:

DECLARE my_id_out INT;
DECLARE field1 INT;
DECLARE field2 INT;

-- do some stuff here to set field1 and field2 variables

SELECT `my_id` INTO my_id_out
FROM `my_table` WHERE `field_1`= field1 AND `field_2`<=> field2;

Sooo… the issue was just that it was using the name of the field, instead of the name of my variable, and comparing field1 = field1.

Just renamed the variable, and all is good…

answered Sep 13, 2013 at 0:51

Paul MolodowitchPaul Molodowitch

1,3563 gold badges12 silver badges29 bronze badges

Источник

The «Result consisted of more than one row» error 1172 in MySQL occurs when a query that is expected to return only one row returns multiple rows instead. This error message can be encountered in various scenarios such as when executing an INSERT statement with a duplicate key, trying to retrieve a unique value from a non-unique field, or attempting to set a variable with a SELECT statement that returns multiple rows. In order to resolve this issue, there are several approaches that can be taken, depending on the specific requirements of your query and the underlying data structure. Below are several methods for fixing the Result consisted of more than one row error 1172 in MySQL.

Method 1: Use LIMIT clause

To fix the «Result consisted of more than one row» error in MySQL, you can use the LIMIT clause to restrict the number of rows returned by the query. Here is an example code:

SELECT column_name FROM table_name WHERE condition LIMIT 1;

This query will only return the first row that matches the condition. If you want to retrieve a specific row, you can use the OFFSET clause along with LIMIT. Here is an example:

SELECT column_name FROM table_name WHERE condition LIMIT 1 OFFSET 2;

This query will return the third row that matches the condition (OFFSET 2 skips the first two rows).

If you want to retrieve all the rows that match the condition, you can use a subquery with the LIMIT clause. Here is an example:

SELECT column_name FROM (SELECT column_name FROM table_name WHERE condition LIMIT 10) AS t;

This query will return the first 10 rows that match the condition.

In summary, to fix the «Result consisted of more than one row» error in MySQL, you can use the LIMIT clause to restrict the number of rows returned by the query. You can also use the OFFSET clause to retrieve a specific row, or a subquery with the LIMIT clause to retrieve all the rows that match the condition.

Method 2: Add a UNIQUE constraint to the table

To fix the «Result consisted of more than one row» error in MySQL, you can add a UNIQUE constraint to the table. This will ensure that each row in the table has a unique value for the specified column(s).

Here’s how you can add a UNIQUE constraint to an existing table:

ALTER TABLE table_name ADD UNIQUE (column_name);

For example, if you have a table called users with a column called email, you can add a UNIQUE constraint to ensure that each email address is unique:

ALTER TABLE users ADD UNIQUE (email);

If there are already duplicate values in the table, you will need to remove them before adding the UNIQUE constraint. You can do this using a DELETE statement:

DELETE FROM table_name WHERE column_name = 'duplicate_value';

For example, to remove a duplicate email address from the users table:

DELETE FROM users WHERE email = 'duplicate@example.com';

Once you have removed any duplicates, you can add the UNIQUE constraint as described above.

Note that adding a UNIQUE constraint may cause other queries to fail if they try to insert duplicate values. You may need to modify your code to handle these errors appropriately.

That’s it! Adding a UNIQUE constraint to your table should fix the «Result consisted of more than one row» error in MySQL.

Method 3: Modify the query to return a single row

To fix the «Result consisted of more than one row» error in MySQL, you can modify the query to return a single row. Here’s an example:

SELECT column_name FROM table_name WHERE condition LIMIT 1;

In this example, column_name is the name of the column you want to select, table_name is the name of the table you want to select from, condition is the condition you want to use to filter the results, and LIMIT 1 limits the result set to a single row.

Here’s another example:

SELECT MAX(column_name) FROM table_name WHERE condition;

In this example, MAX(column_name) returns the maximum value of column_name, table_name is the name of the table you want to select from, and condition is the condition you want to use to filter the results.

You can also use subqueries to return a single row. Here’s an example:

SELECT column_name FROM table_name WHERE column_name = (SELECT column_name FROM table_name WHERE condition LIMIT 1);

In this example, the subquery (SELECT column_name FROM table_name WHERE condition LIMIT 1) returns a single row, which is then used to filter the results of the outer query.

By modifying your query to return a single row, you can avoid the «Result consisted of more than one row» error in MySQL.

Method 4: Ensure data integrity with transactions and subqueries

To fix the «Result consisted of more than one row Error 1172» in MySQL, you can use transactions and subqueries to ensure data integrity. Here’s how to do it in a few steps:

Start a transaction using the BEGIN statement.

Use a subquery to retrieve a single value from the table. For example, to retrieve a single value from the «users» table:

SELECT id FROM users WHERE username = 'john';

Use the retrieved value in an UPDATE or DELETE statement to ensure that only one row is affected. For example, to update the «email» field for the user with the username «john»:

UPDATE users SET email = 'john@example.com' WHERE id = (SELECT id FROM users WHERE username = 'john');

Commit the transaction using the COMMIT statement.

If there are any errors during the transaction, you can use the ROLLBACK statement to undo any changes made.

By using transactions and subqueries, you can ensure that only one row is affected by your queries, avoiding the «Result consisted of more than one row Error 1172» in MySQL.

Источник

Solution 1

I had the similiar issue and when I put table alias it worked like a charm.

SELECT t.tax_amount,t.tax_percentage FROM nepse_tax t

Solution 2

I had this problem and found it went away when I used both table name and column name in select statements, even simple ones.

Solution 3

The issue i had was IN parameter and the column name both were same so altered IN parameter name and it worked

Solution 4

I have experienced the same error in mysql.

MySQL Error 1172 — Result consisted of more than one row

Then I saw the question:

mysql stored procedure error (1172, ‘Result consisted of more than one row’)

But it was not I meant to ask. LIMIT 1;-wasn’t up to my expectation. It will just return the first row for all the case.

Then I started looking at this one deeply and now I got the solution.

This case happend because the code for stored procedure returns multiple rows and that was because I had many extra spaces and tab characters in my code[the code I wrote for stored procedure] and when I removed them with just one/two appropriate tab chars- it was just like a flying machine.

I don’t know if it is the same case that you are experienced with. Anyway give a try.

Thank you.

Comments

I’m getting this error from MySQL when running a query inside a stored procedure:

Error Code: 1172
Result consisted of more than one row

I understand the error: I’m doing a SELECT (...) INTO (var list), and thus the query is required to return a single row. When I use LIMIT 1 or SELECT DISTINCT, the error goes away.

However: when I run the original query manually (without LIMIT or DISTINCT), it does return a single row. So I’m suspecting I may have bumped into a MySQL bug. Does anyone know what could be happening?

EDIT

I’m posting the SQL as requested. Everything that starts with an underscore is a variable declared earlier inside the procedure. When I test it, I’m replacing _cd_pai_vc with the ID for the record that is causing the problem.
```
SELECT  a.valor,    IFNULL(p.valor, 0), fn_cd2alias(ra.cd_registro),    fn_cd2alias(IFNULL(p.valor,0))
INTO    _valor,     _cd_pai_vc,         _alias_verbete,                 _alias_pai
FROM dados_registros ra
    INNER JOIN dados_varchar255 a
    ON a.cd_registro = ra.cd_registro
        AND a.fl_excluido = 0
        AND a.alias = 'vc-verbetes-termo'
    LEFT OUTER JOIN dados_registros rp
        INNER JOIN dados_int p
        ON p.cd_registro = rp.cd_registro
            AND p.fl_excluido = 0
            AND p.alias = 'vc-remissoes-termo referenciado'
        INNER JOIN dados_int pt
        ON pt.cd_registro = rp.cd_registro
            AND pt.fl_excluido = 0
            AND pt.alias = 'vc-remissoes-tipo remissao'
            AND fn_cd2alias(pt.valor) = 'hierarquica'
    ON ra.cd_registro = rp.cd_entidade
        AND rp.fl_excluido = 0
        AND fn_cd2alias(rp.cd_modulo) = 'vc-remissoes'
WHERE ra.cd_registro = _cd_pai_vc 
    AND ra.fl_excluido = 0;
```
- Change the code to either return the multiple rows, or to insert them somewhere. That may give you a hint why it is happening. It is probably not a MySQL bug.
- @IamJohnGalt I gave up understanding the cause, and just added DISTINCT to the query. If I revisit it some day, I’ll post news here.

Recents

Источник

I’m getting this error from MySQL when running a query inside a stored procedure:

Error Code: 1172
Result consisted of more than one row

I understand the error: I’m doing a SELECT (...) INTO (var list), and thus the query is required to return a single row. When I use LIMIT 1 or SELECT DISTINCT, the error goes away.

However: when I run the original query manually (without LIMIT or DISTINCT), it does return a single row. So I’m suspecting I may have bumped into a MySQL bug. Does anyone know what could be happening?

EDIT

I’m posting the SQL as requested. Everything that starts with an underscore is a variable declared earlier inside the procedure. When I test it, I’m replacing _cd_pai_vc with the ID for the record that is causing the problem.

SELECT  a.valor,    IFNULL(p.valor, 0), fn_cd2alias(ra.cd_registro),    fn_cd2alias(IFNULL(p.valor,0))
INTO    _valor,     _cd_pai_vc,         _alias_verbete,                 _alias_pai
FROM dados_registros ra
    INNER JOIN dados_varchar255 a
    ON a.cd_registro = ra.cd_registro
        AND a.fl_excluido = 0
        AND a.alias = 'vc-verbetes-termo'
    LEFT OUTER JOIN dados_registros rp
        INNER JOIN dados_int p
        ON p.cd_registro = rp.cd_registro
            AND p.fl_excluido = 0
            AND p.alias = 'vc-remissoes-termo referenciado'
        INNER JOIN dados_int pt
        ON pt.cd_registro = rp.cd_registro
            AND pt.fl_excluido = 0
            AND pt.alias = 'vc-remissoes-tipo remissao'
            AND fn_cd2alias(pt.valor) = 'hierarquica'
    ON ra.cd_registro = rp.cd_entidade
        AND rp.fl_excluido = 0
        AND fn_cd2alias(rp.cd_modulo) = 'vc-remissoes'
WHERE ra.cd_registro = _cd_pai_vc 
    AND ra.fl_excluido = 0;

Источник

Method 1: Use LIMIT clause

Method 2: Add a UNIQUE constraint to the table

Method 3: Modify the query to return a single row

Method 4: Ensure data integrity with transactions and subqueries

Solution 1

Solution 2

Solution 3

Solution 4

Related videos on Youtube

Comments

Recents