Howto Insert Into Table with Select and Order by using fetch first rows only with DB2 -
i using db2 v7.2 express-c windows , need insert number of rows table select table. im not sure, if issue of old db2 version or if there general problem statement. looking alternative it.
the task insert set of ids table markierung, defined 2 columns
iduser integer not null
idbild integer not null
straight forward insert corresponding items other tables, e.g.
insert markierung select distinct xuser.id userid, xbild.id bildid user xuser, bild xbild, logbook xlogbook xbild.id = xlogbook.oid , xuser.id = xlogbook.userid this approach works fine, want insert number of rows (the newest ones). therefore this:
insert markierung select distinct xuser.id userid, xbild.id bildid, xlogbook.date date user xuser, bild xbild, logbook xlogbook xbild.id = xlogbook.oid , xuser.id = xlogbook.userid order date desc fetch first 5 rows only; first problem: need add date selection using order because want insert 5 newest rows. insert allows 2 columns changed query
select userid, bildid ( select distinct xuser.id userid, xbild.id bildid, xlogbook.date date user xuser, bild xbild, logbook xlogbook xbild.id = xlogbook.oid , xuser.id = xlogbook.userid ) xxx order date desc fetch first 5 rows only; the select statement works fine
when try insert selected results table markierung, error: using statement:
insert markierung select userid, bildid ( select distinct xuser.id userid, xbild.id bildid, xlogbook.date date user xuser, bild xbild, logbook xlogbook xbild.id = xlogbook.oid , xuser.id = xlogbook.userid ) xxx order date desc fetch first 5 rows only; this error:
sql0104n @ "begin-of-statement" unexpected token "insert markierung select userid, ". possible tokens are: "". sqlstate=42601
do have idea, how can insert 5 rows markierung? there alternative way or alternate query inserting?
Comments
Post a Comment