Sql statement error basic

Question

Still getting to grips with sql but this seams like a pretty basic problem im obviously missing something big

SELECT * FROM myTable WHERE arbitraryInt < (SELECT MAX(arbitraryInt));

I get no rows returned when i know for a fact that there are arbitraryInt values that are less than the max. Theres no error though just nothing returned. Thanks in advance

update: this is the rest of the code which still gets an error with FROM keyword

UPDATE myTable SET arbitraryInt = arbitraryInt + 1 
WHERE primaryKey = 0001 AND arbitraryInt < (
    SELECT MAX(arbitraryInt) FROM myTable
);

I get the error myTable is soecified twice

The context is im trying to make items manually sortable using a sorting integer for each item. So the reason for MAX is to keep that integer from getting bigger than necessary. I'm sure theres a bette way to do this but i havent seen it. The result im looking for is a table with up and down arrows to re order list items for an online store.

It seems odd to me that you can update a selected cursor. That might be your problem. — Sebas, May 01 '16 at 01:38
By sorting like that you'll end up incrementing some `arbitraryInt` value so that it matches `MAX(arbitraryInt)` and eventually they will all equal that `MAX()`. I think you should allow the integer value to get bigger. An integer will give you over 2 billion numbers. — user212514, May 01 '16 at 03:37

score 0 · Answer 1 · answered May 01 '16 at 01:13

0

A subquery must be a complete query, capable of being run on its own. Your subquery

(SELECT MAX(arbitraryInt))

is not complete, as it does not specify a FROM clause. Your complete query should probably be something like

SELECT *
  FROM myTable
  WHERE arbitraryInt < (SELECT MAX(arbitraryInt)
                          FROM myTable);

answered May 01 '16 at 01:13

Darwin von Corax

5,201
3
17
28

Thanks, that fixed this part of the query.. In context i now have UPDATE myTable SET arbitraryInt = arbitraryInt + 1 WHERE primaryKey = 0001 AND arbitraryInt < (SELECT MAX(arbitraryInt) FROM myTable); I get the error myTable is soecified twice – Felix May 01 '16 at 01:18

score 0 · Answer 2 · answered May 01 '16 at 01:16

0

You are missing a from clause, so presumably you intend:

SELECT *
FROM myTable
WHERE arbitraryInt < (SELECT MAX(arbitraryInt) FROM mytable);

answered May 01 '16 at 01:16

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · edited May 23 '17 at 12:23

This has feature in stack overflow before. Have a look at MYSQL update with WHERE SELECT subquery error

The following code seems to be something like what you are asking for
/*
CREATE TABLE MYTABLE (PRIMARYKEY INT, ARBITRARYID INT, AB1 INt)
*/

truncate table MYTABLE;
INSERT INTO MYTABLE
VALUES (1,1,NULL),(1,2,NULL),(1,3,NULL);

update  MYTABLE 
set     ARBITRARYID = ARBITRARYID + 1
where   PRIMARYKEY = 1 and
        ARBITRARYID <=  (
        SELECT MAXABID FROM
        (
        select max(ARBITRARYID) MAXABID FROM MYTABLE
        ) S
        )
;
select * from MYTABLE

Sql statement error basic

3 Answers3