UPDATE TABLE Based on Condition

[jhanit]

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

[jtaylor-sfdc]

@jhanit - can you give me a specific example where UPSERT SELECT doesn't meet your needs?

[jtaylor-sfdc]

Closing this as I still don't understand why UPSERT SELECT cannot be used.

[jhanit]

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.

Sample Upsert select query(which is working fine)

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;

Issue-

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?

Query

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

[saigeethamn]

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?