SET @new_id = 0;
UPDATE usertable
SET id = (@new_id := @new_id + 1)
ORDER BY id;
I am trying to run this Query, Also I have tried to run several other named queries which has two or more queries, but no one runs. Only single Query is running in one named query.
Please help.
Hi,
See the below post, I know you are using a NamedQuery but the same applies :
If you are updating a record, you need a WHERE clause instead of an ORDER BY clause.
An UPDATE statement without a WHERE clause will update every row in the table to have an ID of 1 (0 + 1).
If you really want to update every row in the table (I'm guessing you want each row to have a unique ID), you'll need to add a subquery complication or do some looping.
ORDER BY is used in SELECT statements that retrieve multiple rows.
You can check your SQL directly in the database or use Ignition's
Tools / Database Query Browser
and make sure your query works in the native environment before writing it into a NamedQuery.
If you need a SQL Script to run your best bet is putting it into a stored procedure and calling that stored procedure from the named query or via scripting.
