Hello.
Using SQL like below
If there is a record that matches the conditions, update it,
If not, I would like to do additional processing.
[SQL]
UPDATE Hoge SET HogeCD = '001', HogeName = 'suzuki'
WHERE HogeID = 10000
AND (HogeCD <> '001' OR HogeName <> 'suzuki')
IF @@ ROWCOUNT = 0
INSERT INTO Hoge (HogeID, HogeCD, HogeName)
However, the following error occurs and it cannot be implemented successfully.
If the method using @@ ROWCOUNT can be implemented, please teach me how.
By the way, SQLServer is used.
Thank you.
You read the data using an aggregate, if the data you receive for update already exists you have the I'd in the aggregate. If the record does not yet exists then it will be s null identifier.
Daniël Kuhlmann wrote:
thank you for your answer.I was able to implement it by the method you taught me.Thank you very much.
https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/
this wrote:
Thank you for your reply.Of course, I considered implementing SQL using MERGE.The database of data to be added or updated is MySQLBecause the addition or update destination is SQLServerThe following error will occur and it will not work.
I'm sorry, but please let me know if there is a good way to use MERGE.
Hi,
Check this post to see if it works for you.
Regards,
Marcelo
Marcelo Ferreira wrote:
Thank you for your reply.I saw the URL you presented.I set it to a parameter and implemented it.It was not possible to implement it well.Please let me know if the method is different.
Sorry for the Japanese site,I want to implement what is written on this site.
https://www.ilovex.co.jp/blog/system/cat820/sqlserversql.html
Rio,
Probably not what you want to hear but why not use aggregates and CreateOrUpdate Server Action?
Stefano Valente wrote:
Reasons for not using CreateOrUpdate Server ActionThe process we are going to do this time is a batch that is executed at the date and time.Implement the processing.1: Collect data from external data table at once.2: Update or add the acquired data to the entity created in Service Studio of Outsystems.If you use CreateOrUpdate Server Action,What should Id specify for the automatically generated entity item?
Rio Toyoda wrote:
Its just like Daniel says. One of the cool things in Outsystems is just this feature.
You query in the aggregate. If a record is found, you can use that to update. The CreateOrUpdate action will then do the update because the record has an ID. If the aggregate doesnot find a record, you can use it to set your desired values. The CreateOrUpdate action will create a new record, because the record has no ID.
Thank you for the easy-to-understand supplementary explanation.I am grateful. Thank you.
Hi Rio,
What Stefano said is true. I only show you how to use @@ROWCOUNT but if you should use it is another matter. If your requirement is just update a record and if it doesn't exist create one you shouldn't use SQL for that. You should use Outsystems functionalities. I recommend you to look into this video.
NOTE: For this to work, you MUST set "Expand Inline" to TRUE.
It essentially uses an SQL injection bug.