Query Problem

Tools
Enthusiast

Query Problem

update tabl_name
set
id = ( sel id as SCI
From cycle_Calendar
Where cde ='AC'
And id = (sel Min(id)
From cycle_calendar
Where cde = 'AC'
And ST_cde = -20))

When I execute this query, I get Syntax error. Expected something between '(' and 'sel'
1 REPLY
Enthusiast

Re: Query Problem

To begin with, you cant give a select query on the RHS of the set clause, that's why you got the syntax error.

you will have to use the join syntax of update.

There's no where clause for your update, which means all records in tabl_name will have their id column updates to the same value... is that what you want ?

Next ..

sel Min(id)
From cycle_calendar
Where cde = 'AC'
And ST_cde = -20

will return only on record ... BUT

sel id as SCI
From cycle_Calendar
Where cde ='AC'
And id = (sel Min(id)
From cycle_calendar
Where cde = 'AC'
And ST_cde = -20)

could return multiple records if (cde,id) combo is not unique (because you are not using ST_CDE in the outer where clause. That could generate an error (runtime) in the UPDATE (and you might not catch it for a while).