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.

https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

Hi,

Check this post to see if it works for you.

Regards,

Marcelo

Rio,

Probably not what you want to hear but why not use aggregates and CreateOrUpdate Server Action?



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. 

Regards,

Marcelo

this wrote:

https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

Thank you for your reply.
Of course, I considered implementing SQL using MERGE.
The database of data to be added or updated is MySQL
Because the addition or update destination is SQLServer
The 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.


Marcelo Ferreira wrote:

Hi,

Check this post to see if it works for you.

Regards,

Marcelo

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



Stefano Valente wrote:

Rio,

Probably not what you want to hear but why not use aggregates and CreateOrUpdate Server Action?



Reasons for not using CreateOrUpdate Server Action
The 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?


Solution

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.


Solution

Rio Toyoda wrote:

Stefano Valente wrote:

Rio,

Probably not what you want to hear but why not use aggregates and CreateOrUpdate Server Action?



Reasons for not using CreateOrUpdate Server Action
The 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,

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.



Daniël Kuhlmann wrote:

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.


thank you for your answer.

I was able to implement it by the method you taught me.

Thank you very much.


Stefano Valente wrote:

Rio Toyoda wrote:

Stefano Valente wrote:

Rio,

Probably not what you want to hear but why not use aggregates and CreateOrUpdate Server Action?



Reasons for not using CreateOrUpdate Server Action
The 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,

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.