MYSQL to SQL - Limit in Update

Question

I try to change my code from MYSQL to SQL and i got an error (SQL Syntax 'Limit').

So i tried to change my query and update with "TOP" but seems to work only with SELECT.

So, how can i change this MYSQL query :

$fct="UPDATE `users` SET `STREAM_TITRE` = '$STREAM_TITRE',`STREAM_URL` = '$STREAM_URL',`STREAM_DESC` = '$STREAM_DESC',`STREAM_GENRE` = '$STREAM_GENRE' WHERE `ID` =$IDSESS LIMIT 1";

Here is my SQL Code without Limit :

$fct="UPDATE users SET STREAM_TITRE = '$STREAM_TITRE', STREAM_URL = '$STREAM_URL', STREAM_DESC = '$STREAM_DESC', STREAM_GENRE = '$STREAM_GENRE' WHERE ID = '$IDSESS'";

Thanks

I doubt that SQL Syntax 'Limit' is your complete error? And what is the problem? It seems to me you have a query that doesn't work, and have one that does, so what is the problem is you have one that works? — Nanne
– Nanne, Commented Apr 10, 2012 at 10:05
What version of SQL Server are you using? The documentation I've seen states that you can use TOP with UPDATE. Does it simply not work, or do you get an error message? — Rory Hunter
– Rory Hunter, Commented Apr 10, 2012 at 10:18
What do you mean with "to SQL"? SQL is a language that is also used by MySQL. — user330315
– user330315, Commented Apr 10, 2012 at 12:15

Erwin Brandstetter · Accepted Answer · 2012-04-10 19:15:03Z

5

It's not very clear which version of your query is working and which is not - and in what DBMS.

If ID is of char or varchar type, you are missing some quotes in the LIMIT version. Although MySQL is not very picky and you won't have many issues, with or without quotes:

$fct = "
  UPDATE users 
  SET STREAM_TITRE = '$STREAM_TITRE'
    , STREAM_URL = '$STREAM_URL'
    , STREAM_DESC = '$STREAM_DESC'
    , STREAM_GENRE = '$STREAM_GENRE' 
  WHERE ID = $IDSESS              --<-- this should be '$IDSESS' , right?
                                  ----- or $IDSESS , depending on the datatype
  LIMIT 1
       ";

Note: The LIMIT n works in MySQL and PostgreSQL, but not in some other DBMS. Plus, I don't think you really need it anyway, as the ID is probably the Primary Key of the table.

If you are trying to convert the statement from MySQL to SQL-Server, you should not use the backquotes and replace LIMIT 1 with TOP (1):

$fct = "
  UPDATE TOP (1) users 
  SET STREAM_TITRE = '$STREAM_TITRE'
    , STREAM_URL = '$STREAM_URL'
    , STREAM_DESC = '$STREAM_DESC'
    , STREAM_GENRE = '$STREAM_GENRE' 
  WHERE ID = $IDSESS             
       ";

edited Apr 10, 2012 at 19:15

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

answered Apr 10, 2012 at 10:11

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

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

2 Comments

Champi Ardon Over a year ago

Changed it thanks. If you find a SQL Version, it would be perfect but thanks ;)

ypercubeᵀᴹ Over a year ago

What do you mean with "SQL version"? Microsoft SQL-Server?

Collectives™ on Stack Overflow

MYSQL to SQL - Limit in Update

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related