Closed jhanit closed 10 years ago
@jhanit - can you give me a specific example where UPSERT SELECT doesn't meet your needs?
Closing this as I still don't understand why UPSERT SELECT cannot be used.
Hi James,
1)- Sorry for not responding for a long time(traveling)
Here you go with sample where i think(may be wrong) Upsert select is not able to replace Update of sql.
UPSERT INTO TEMPENTITY(ENTITY,EVENTTIME) select ENTITY,MAX(EVENTTIME) as EVENTTIME from EVENT_MANAGEMENT(EVCOL_NO_OF_PARTS INTEGER,EVCOL_STANDARD_COST FLOAT,EVCOL_INVENTORY INTEGER,EVCOL_TYPE VARCHAR) where ENTITY is not null and EVCOL_INVENTORY is not null group by ENTITY;
I can only select existing value from EVENT_MANAGEMENT(sample query above) based on where condition and i can only put those value in Table TEMPENTITY.
But actually by using update command of sql i can update a table with new value based on some where clause. Can we achieve this?
UPDATE TEMPENTITY SET ENTITY = value1, EVENTTIME = value2 WHERE ENTITY is not null and EVCOL_INVENTORY is not null group by ENTITY;
where value1 and value2 is new value not a value from any existing table EVENT_MANAGEMENT.
In nut shell i was looking for "where" clause with UPSERT VALUES not with UPSERT SELECT (reason as explained above).
Please feel free to correct me if i am wrong in my analysis or ask for more info in case of ambiguity.
--@james- Over to you to keep it open or close this issue. :-)
Thanks, Nitin
Hi this seems like a issue closed long time but does not answer how we can do an UPSERT based on a condition like "UPSERT INTO EMP (NAME) VALUES ('XXX') WHERE EMPID = 123; Instead we seem the need to have a table from which we can select and upsert. Can we have a feature to just upsert based on a condition?
Hi, We have , 1)UPSERT which update or insert a table ~ equivalent to insert of SQL+ Update of sql without condition.
2)UPSERT SELECT- can update table based on condition. looks similar to SQL update statement. But major difference is we can't update a table with new set of values as select will work on existing table i.e update temp with existing table values.
Common use case/ Issue:- Update a table with new set of values based on condition, i.e UPSERT based on condition.
Can we have this common but very important feature of SQL? Thanks, Nitin