Update SQL with Inline View

I need help on this update query on Informix 11.70.FC4: Objective : ============ Select the rows which is the smallest MYDTTM value in the same timezones, and if MYDTTM is not 1900/1/1 00.00.00, change it to that. SQL ( below is the SQL I wrote based on Oracle synatx, but its giving error =================================================== update ( select * from TabA where (timezone, MYDTTM) in ( select timezone, min (MYDTTM) as min_MYDTTM from TabA group by timezone ) ) set MYDTTM = '1900-01-01 00:00:00' where MYDTTM::datetime year to second !='1900-01-01 00:00:00' Please help me modifying this query for above update on Informix. Thanks,

Can someone help me on this...?

On Thu, Sep 6, 2012 at 8:06 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> wrote: > I need help on this update query on Informix 11.70.FC4: > > Objective : > ============ > Select the rows which is the smallest MYDTTM value in the same > timezones, and if MYDTTM is not 1900/1/1 00.00.00, change it to that. > So, it would be acceptable, even if unnecessary, to simply set the MYDTTM to 1900-01-01 00:00:00 in all the cases that match the criteria. It would be helpful if you provided us with the schema of the table, at least to the point where we can understand why the cast is necessary on the MYDTTM column. Some sample data would be helpful too; it doesn't need to be a lot, but sufficient (5-10 rows). > SQL ( below is the SQL I wrote based on Oracle synatx, but its giving error > =================================================== > > update ( select * from TabA where (timezone, MYDTTM) > in ( select timezone, min (MYDTTM) as min_MYDTTM from TabA > group by timezone ) ) > > set MYDTTM = '1900-01-01 00:00:00' > where MYDTTM::datetime year to second !='1900-01-01 00:00:00' > > Please help me modifying this query for above update on Informix. > The first step is to write a SELECT statement that generates the rows to be updated. You seem to have this query (which should look sanely indented if you get constant-width font; no promises otherwise): SELECT * FROM TabA WHERE (timezone, mydttm) IN (SELECT timezone, MIN(mydttm) AS min_mydttm FROM TabA GROUP BY timezone) The '(timezone, mydttm) IN (...)' notation is in SQL-92, but is not supported by Informix (and the main reason it isn't in there is that no-one has asked sufficiently persistently for it). That's a '<row value constructor>' in the SQL grammar. So, step 1 is to rewrite the query so it produces the correct result in Informix: SELECT * FROM TabA AS a1 JOIN (SELECT timezone, MIN(mydttm) AS mydttm FROM TabA GROUP BY timezone) AS a2 ON a1.timezone = a2.timezone AND a1.mydttm = a2.mydttm Not very different in many respects. You can easily add the extra constraint about a2.mydttm not being the reference date if so desired. The rows to be updated are identified by the sub-query, assuming that the combination of time zone and mydttm is unique. (Actually, if it isn't unique, the update will still work, but if there are multiple rows for a given time zone with the same minimal mydttm value, they will all be set to the reference date.) Now we need to update the rows selected by that query. It would be useful to be able to use the row value constructor notation: -- Not valid Informix SQL UPDATE TabA SET mydttm = '1900-01-01 00:00:00' WHERE (timezone, mydttm) IN (SELECT timezone, MIN(mydttm) AS mydttm FROM TabA GROUP BY timezone); This leaves me wondering what your Oracle SQL looked like, and why it looked like as complex as it presumably did. Why an inline view at all? How to do it in Informix? This works for me, but is a little indirect because of the temporary table: SELECT timezone, MIN(mydttm) AS mydttm FROM TabA GROUP BY timezone INTO TEMP TabB; UPDATE TabA SET mydttm = '1900-01-01 00:00:00' WHERE EXISTS (SELECT * FROM TabB WHERE TabA.timezone = TabB.timezone AND TabA.mydttm = TabB.mydttm); Of course, I didn't have any concurrency issue to worry about. Superficially, it might be possible to use something like: UPDATE TabA SET mydttm = '1900-01-01 00:00:00' WHERE EXISTS (SELECT * FROM TabA AS a1 WHERE a1.timezone = tabA.timezone HAVING MIN(a1.mydttm) = tabA.mydttm GROUP BY a1.timezone ); However, there are problems with 'what does TabA mean in the sub-query', and it doesn't mean what it must mean for the query to work. I get a -201 syntax error from that update. So, to deal with the concurrency issue, you can use a variant on this trace output from SQLCMD: + begin; + drop table TabA; + CREATE TABLE TabA ( timezone VARCHAR(20) NOT NULL, mydttm DATETIME YEAR TO SECOND NOT NULL, PRIMARY KEY(timezone, mydttm) ); + INSERT INTO TabA VALUES('UTC', '1999-12-31 23:59:59'); + INSERT INTO TabA VALUES('UTC', '2012-01-01 00:00:00'); + INSERT INTO TabA VALUES('PDT', '1999-12-31 23:59:59'); + INSERT INTO TabA VALUES('PDT', '2012-01-01 00:00:00'); + INSERT INTO TabA VALUES('EDT', '1900-01-01 00:00:00'); + INSERT INTO TabA VALUES('CDT', '1850-01-01 00:00:00'); + SELECT * FROM TabA ORDER BY timezone, mydttm; CDT|1850-01-01 00:00:00 EDT|1900-01-01 00:00:00 PDT|1999-12-31 23:59:59 PDT|2012-01-01 00:00:00 UTC|1999-12-31 23:59:59 UTC|2012-01-01 00:00:00 + SELECT timezone, MIN(mydttm) AS mydttm FROM TabA GROUP BY timezone; CDT|1850-01-01 00:00:00 EDT|1900-01-01 00:00:00 PDT|1999-12-31 23:59:59 UTC|1999-12-31 23:59:59 + LOCK TABLE TabA IN SHARE MODE; + SELECT timezone, MIN(mydttm) AS mydttm FROM TabA GROUP BY timezone INTO TEMP TabB; + UPDATE TabA SET mydttm = '1900-01-01 00:00:00' WHERE EXISTS (SELECT * FROM TabB WHERE TabA.timezone = TabB.timezone AND TabA.mydttm = TabB.mydttm); + SELECT * FROM TabA ORDER BY timezone, mydttm; CDT|1900-01-01 00:00:00 EDT|1900-01-01 00:00:00 PDT|1900-01-01 00:00:00 PDT|2012-01-01 00:00:00 UTC|1900-01-01 00:00:00 UTC|2012-01-01 00:00:00 + rollback; Note the ability to start a transaction, drop a table, create a new table with the same name, populate and use the table, then rollback the whole transaction. You can't do that in Oracle! (It isn't often you need to, either, but it was useful for this demonstration.) -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04089131bf150404c93556cc

Thanks so much.