#Sql Queries0 discussing

Hi, @Anirudh venugopal

In Marketing Cloud, the UPDATE statement is not supported; only the SELECT statement is supported. Therefore, when configuring a SQL Query Activity, you can choose Update under Data Action. If you choose to use Update, it is necessary to set a primary key within the data extension.

Hi, @Anirudh venugopal

The function GetDate() displays the time up to seconds, so it is necessary to use conversion functions like Convert to eliminate the display of hours, minutes, and seconds.

Additionally, GetDate() is presented in the CST (Central Standard Time) timezone. Therefore, instead of simply adding one day, you should add the time corresponding to your timezone. In my country, Japan, we use JST (Japan Standard Time), and there is a 15-hour difference with CST. So, to represent one day later, you should add 15 hours to 24 hours. (= 39 hours.)

Please adjust the logic according to your country's timezone:

CHKOUT_DT = Convert(Date,DateAdd(hh,39,GetDate()),111)

Hello @Anirudh Venugopal Warrier,

Primary key violations occur when an attempt is made to insert a record with a primary key value that already exists in the table. Here are a few ways to resolve primary key violations in SQL queries:

1. Check the input data: Ensure that the input data is correct and does not contain any duplicate values. If the input data is coming from a file or another database, verify that it is correct and does not contain any errors.
2. Use a MERGE statement: Use a MERGE statement to update existing records and insert new records. A MERGE statement can be used to perform both INSERT and UPDATE operations in a single statement.
3. Use a subquery: Use a subquery to check if the record already exists before inserting it. The subquery can check for the existence of the primary key value in the table and return a boolean value that can be used to determine whether to insert the record or not.
4. Use an UPSERT statement: An UPSERT statement is a combination of an INSERT and an UPDATE statement. It will try to insert a record, and if it already exists, it will update the existing record.
5. Use a transaction: Wrap the INSERT statement in a transaction to ensure that the primary key violation is caught and handled correctly. This will ensure that the entire operation is either committed or rolled back in case of an error.
6. Remove the primary key constraint: If you do not need the primary key constraint, you can remove it from the table. However, this is not recommended as it can lead to data inconsistencies and make it difficult to maintain the integrity of the data.

By using one of these methods, you should be able to resolve the primary key violation issue in your SQL query. 

If you want to know the records of the remaining paths, update a Marketing Cloud contact record in Journey Builder using the "update contact" activity. And you can query the DE.

Hello @Anirudh Venugopal Warrier,

SELECT *
FROM [Your Data Extension]
WHERE DateField >= DATEADD(day, 30, GETDATE())

Replace "Your Data Extension" with the name of your data extension, and "DateField" with the name of the date field you want to filter by. This query will select all records where the value in the "DateField" is equal to or greater than 30 days ahead of the current date.