The infrastructure of our system looks like this. An AWS lambda function receives requests such as (accountId, .....). It creates an entry in the MySQL database using a newly generated UUID as caseId. (caseId, accountId, ....). The insert is a conditional insert operation discussed in detail below. I am able to avoid race condition by setting transaction isolation to SERIALIZABLE. However, the issue is that I do not have any control over how many concurrent requests will be successfully processed. For example, consider following concurrent requests.

request  | accountId | field1 | ...  
 1         a1          value1   ....   true     --- create a new entry with caseId Idxxx
 2         a1          value2   ....   false    --- update existing entry with caseId Idxxx 
 3         a1          value3   ....   false    --- update existing entry with caseId Idxxx 
 4         a1          value4   ....   false    --- update existing entry with caseId Idxxx

With our current implementation we are getting CannotAquireLockException. What are the ways in which I can avoid retry failures (CannotAquireLockException) ? The detailed table schema and condition are described below: The database is a mysql database system with the following table schema.

Table1: case table

|caseId(PK) | accountId | status |  .....

Table2: case reopen table

|caseId(FK)| casereopenId(PK)| caseReopenTime|

Table3: Alert table

Id (incrementing id) | alertId | accountId |

The lambda function tries to "create" a case in the database. the create wrapper, generates a UUID for caseId. The goal is : - check if an accountId already exists in case table. - if it does, then - check if status is OPEN - get the caseId for the accountId. - check if the caseId is present in case reopen table. - if above condition is false, then add an entry into the case table. Thanks!

In my Postgres 15 database, I have a table with several geometric columns. I want a trigger to run on the field index_per when a specific geometric field is not null: geom_l93. I have created a function and a trigger:

`
CREATE OR REPLACE TRIGGER check_periode_hierarchie
BEFORE INSERT OR UPDATE ON activite.uniteobservation
FOR EACH ROW
WHEN (NEW.geom_l93 is not null)
EXECUTE FUNCTION activite.valider_periodes_hierarchie()

` I want the trigger to run **only** when geom_l93 is **not NULL**, on an INSERT or UPDATE. But, when I insert or update with NULL in the column geom_l93, the trigger runs anyway. I tried and failed to solve this issue with some intermediate functions and triggers like:

CREATE OR REPLACE FUNCTION activite.check_geom_l93_trigger()
RETURNS trigger AS
$$
BEGIN
    IF NEW.geom_l93 IS NOT NULL THEN
        PERFORM activite.valider_periodes_hierarchie();
    END IF;
    RETURN NEW;
END;
$$

CREATE or replace TRIGGER check_periode_hierarchie
BEFORE INSERT OR UPDATE ON activite.uniteobservation
FOR EACH ROW
EXECUTE FUNCTION activite.check_geom_l93_trigger();

EDIT : I did my best with a fiddle : https://dbfiddle.uk/YytPzEZq?hide=40 EDIT 2 : I disabled other existing triggers on the table one by one to see if there was a "ninja bug", but each time the trigger works on insert whatever the geometric column.

I have a big database which contain a table with about 26 million records. Now i need to make some reports on it from a special period of time. So how to make a replication from that table from a special time, for example, just show replicate the records from 2015-01-01 till now?

I want to create a trigger to fill a field according to acondition as below description For example: I have a table for all insps that have attachments named: “inspectionAttachments” and I have another table “inspections” that contains all insps and I want to fill for example column/field named “shop” in the inspections table to define which insps that have attachments or not So, I want to fill the shop field with Yes if at least this insp has one attachment and fill this field with No if there is no attachement How I can write the trigger to make what I want Thanks for any help 😊 Best, Lubna

I take a Derived Column Transformation to replace a value as soon as a condition on two other columns is met. How can I see in the data flow how often a value changes by this replacement? Is there a way to show it on the output arrow, or how else can I show the changes inside the data flow? #### PS (not needed for the Q/A) I want to check whether the string input is the same as the lookup output. But sometimes, names (first names and last names) have more than one name in the value, like Taylor-Smith against Smith, or Bob Thomas against Bob. People might marry or might in times fill their two first names so that data might not match over two master data accounts.

(DT_BOOL)(FINDSTRING(name,[Lookup.name],1)) ? 1 : [score_name]

The value of column score_name is replaced by 1 as soon as the name is found inside the Lookup.name, else the value is kept which is between 0 and 1. "Name in Lookup?": You could take any other example like [Replace column values?](https://dba.stackexchange.com/questions/47980/replace-column-values) , the question does not depend on what is done.

Why is this so tricky, what is token set to that it isn't equal to null nor an empty string? SELECT lexemes FROM ts_debug('This is a title') WHERE alias = 'asciiword'; lexemes --------- {} {} {} {titl} (4 rows) Ok.. So I want to get rid of {}, SELECT lexemes FROM ts_debug('This is a title') WHERE alias = 'asciiword' AND lexemes '{}' AND lexemes ARRAY[]::text[] AND lexemes IS NOT NULL AND lexemes ARRAY[' '] AND lexemes ARRAY[null]::text[]; **I knew most of these wouldn't work.**, but I'm totally confused why '{}' wouldn't work not ARRAY[]::text;. How do I filter this out?

Looking at the below query, SELECT null IS NOT DISTINCT FROM null AS indf, null = null AS eq; indf | eq ------+---- t | From this we can see the result of the IS NOT DISTINCT FROM is true, and the result of the eq is false. The following then logically follows from that, -- returns 1 row. SELECT * FROM ( VALUES (null) ) AS t(a) JOIN ( VALUES (null) ) AS g(a) ON t.a IS NOT DISTINCT FROM g.a; -- returns 0 rows. SELECT * FROM ( VALUES (null) ) AS t(a) JOIN ( VALUES (null) ) AS g(a) ON t.a = g.a; Because if the condition returns null the join fails. But, this throws me off, with [*row-wise comparison*](https://www.postgresql.org/docs/current/static/functions-comparisons.html#ROW-WISE-COMPARISON) -- returns 1 row. SELECT * FROM ( VALUES (null) ) AS t(a) JOIN ( VALUES (null) ) AS g(a) ON (t) IS NOT DISTINCT FROM (g); -- also returns one row. SELECT * FROM ( VALUES (null) ) AS t(a) JOIN ( VALUES (null) ) AS g(a) ON (t) = (g); Why does [*row-wise comparison*](https://www.postgresql.org/docs/current/static/functions-comparisons.html#ROW-WISE-COMPARISON) treat nulls different than scalar comparison? And is there a point of IS NOT DISTINCT FROM in row-wise comparison?

I am working on something and found this SQL that I am not sure how to rewrite correctly, meaning using PHP PDO. The SQL looks like:

$sql = 'SELECT * FROM table WHERE column ' . isset($variable) ? . '=' . $variable : '>0';

Basically what the query is telling is: if $variable is defined (within the PHP world) then use an = for the WHERE condition, if it is not then use the >0. I can clean up that a little bit on PHP and do something like:

$where = $variable ? 'column = ?' : column > ?'; // ternary operator to build the proper where condition
$sql = 'SELECT * FROM table WHERE $where';
$db->row($sql, [$variable ?? 0]); // bind parameters to the query, PDO way, and the operator will use the value of $variable if it is defined otherwise it will use 0

and it will work fine, I guess. Now, I wonder if I can achieve the same using plain SQL like a condition inside the WHERE same as within the SELECT, and if so, is it optimum? Or programmatically is better and faster?

In traditional programming there is an axiom that states, "do not use errors for flow control". A general example is to throw an error then catch the error instead of using an ordinary conditional statement or break statement. This is harmful because the application has to unwind the call stack and invoke some relatively expensive exception handling logic versus simply handling a conditional statement. I am working with a Postgres system where a user is calling a function in Postgres that throws an error instead of returning no rows when a condition is not met. The condition is roughly a "this input value does not exist, nothing to do" as opposed to a truly exceptional case. Does Postgres incur runtime costs when an error is thrown in a similar manner traditional programming? In other words is using exceptions as flow control in Postgres harmful to performance or just sloppy?

Trying to use a trigger to a) fill in a new column top_category with values left of a colon, found in category, then b) If there is no colon, then put entire insert value into top_category. I can't get this second trigger structured to create. https://dbfiddle.uk/jwLle13e

CREATE TABLE qn_txs (
  id SERIAL PRIMARY KEY,
  category VARCHAR(250),
  top_category VARCHAR(250)
);

-- Stage 1 trigger works
--  CREATE TRIGGER t_add_top_category1 BEFORE INSERT ON qn_txs 
--    FOR EACH ROW 
--  SET NEW.top_category = LEFT(NEW.category, INSTR(NEW.category, ":") - 1);

DELIMITER //
CREATE TRIGGER t_add_top_category2 BEFORE INSERT ON qn_txs 
     FOR EACH ROW BEGIN
         SET NEW.top_category = LEFT(NEW.category, INSTR(NEW.category, ":") - 1)
         -- make sure top_category is not null if there is a value in category
            IF (NEW.top_category = '')
             THEN
              SET NEW.top_category = NEW.category;
           END IF;
END//

DELIMITER;

INSERT INTO qn_txs (category)
VALUES ('I should also be a top cat'), ('bananas:green'), ('baseball:table'), (''), ('Very long string: with spaces');

SELECT * FROM qn_txs;

Here's a table of the desired output after the insert is complete. | id | category | top\_category | |---:|:---------|:-------------| | 1 | I should also be a top cat | I should also be a top cat | | 2 | bananas:green | bananas | | 3 | baseball:table | baseball | | 4 | | | | 5 | Very long string: with spaces | Very long string |

I need to conditionally raise an error, but I can only use a simple statement and no stored procedure. I'd like to do something like this: select case when foo = "bar" then 1 else SIGNAL SQLSTATE 'ERROR' end; Unfortunately SIGNAL is only usable in triggers and procedures and I have to use this within an existing application that only allows me to enter statements, but not procedures. (I only have one long line and no way to set a DELIMITER etc.) Is there any other way to conditionally cause a runtime error ?

create or replace TRIGGER trigger_name ... ... BEGIN IF SYS_CONTEXT (...) IN (...) THEN IF SYS_CONTEXT (...) NOT IN (...) THEN IF SYS_CONTEXT (...) NOT IN (...) THEN INSERT INTO owner.table_name (...) VALUES (...); COMMIT; RAISE_APPLICATION_ERROR(...); END IF; END IF; END IF; END; As I mentioned above, there is a trigger. Trigger should run and insert in the table when it doesn't provide even 1 of the if condition containing 2 not ins. However, when 1 of these 2 not in conditions is met, the trigger does not work as I want. How can I provide this? If even 1 of the 2 conditions is not met, the trigger should run. The following table describes the logic of the trigger. * *1st condition is met* means that SYS_CONTEXT (...) IN (...). * *2nd condition is met* means that SYS_CONTEXT (...) NOT IN (...). * *3nd condition is met* means that SYS_CONTEXT (...) NOT IN (...). Table: 1st condition is met | y | y | ... 2nd condition is met | y | y | 3rd condition is met | y | y | execute insert | ? | ? |

I need a MySQL trigger that can prevent update of a particular column data, if some specific data is available in another table. I have two tables product and sales. **Product** **Sale** I need to prevent the update of updated_at column in product table if the product_id is available in sale table.

In the Oracle DB there are three tables, see below. Table: *'People'* Table: *'Dogs'* Table: *'Cats'* Here is a Fiddle with data to test : http://sqlfiddle.com/#!4/d7cb4 Depending on the result of a query from the *'People'* table, how can I proceed with a different query to request data further either from *'Dogs'* or *'Cats'*. Simply saying I need to execute a corresponding query depending on the answer of the initial/main query. Here is what I tried, (which obviously does not work): WITH CONDITION_CHECK AS ( SELECT ADORE FROM PEOPLE WHERE ADORE = 'dog' --here I will put my variable ) SELECT CASE WHEN CC.ADORE = 'dog' THEN (SELECT * FROM DOGS) WHEN CC.ADORE = 'cat' THEN (SELECT * FROM CATS) ELSE NULL END FROM CONDITION_CHECK AS CC What is the most common approach to set up a conditional statement in Oracle DB? And how can one execute it? I have seen the [IF-THEN-ELSE Statement](https://www.techonthenet.com/oracle/loops/if_then.php) , but I do not understand it much. ---------- **References:** - [Performing a query on a result from another query?](https://stackoverflow.com/questions/949465/performing-a-query-on-a-result-from-another-query) - [Using IF ELSE statement based on Count to execute different Insert statements](https://stackoverflow.com/questions/14072140/using-if-else-statement-based-on-count-to-execute-different-insert-statements) - [IF Condition Perform Query, Else Perform Other Query](https://stackoverflow.com/questions/17199604/if-condition-perform-query-else-perform-other-query)

I am in the process of restructurizing and unifying a large database in PostgreSQL. One part of this is to split up some large tables into smaller ones according to their value in 'key'. The function I have written for this works well and looks like this:

CREATE OR REPLACE FUNCTION split_tables (table_string varchar(100)) RETURNS void AS
$$
DECLARE
r_row record;

BEGIN
FOR r_row IN SELECT type FROM type_list
LOOP
EXECUTE 'CREATE TABLE public.id_' || table_string || '__' || r_row.type || ' AS (SELECT * FROM id_'
|| table_string || ' WHERE lower(id_' || table_string || '.key) = lower(''' || r_row.type || '''));';
END LOOP;
END;
$$
LANGUAGE plpsql;

So, this goes through all entries in type_list and creates tables for each type. To do this for all tables that need to be split I call this function with SELECT tobesplit.id FROM tobesplit, LATERAL split_tables(tobseplit.id); However, this creates tables for ALL entries in type_list, even if there are no entries in the table to be split for some types. So in the end, I create a whole bunch of empty tables that I don't need. I looked for ways to only create tables if the result is not empty, and found a possible solution in the answer for: https://dba.stackexchange.com/questions/284449/create-table-if-only-not-empty-result-set . This works if I test it seperately, but I have no idea how to incorporate this into the exectue statement. I always get syntax errors as soon as I write an IF or CASE-clause into the EXECUTE statement. I am thankful for any ideas :)

In the database application I have worked on, all join conditions are binary, like select * from A join B join C on A.id=B.id and B.orderno = C.orderno Each condition, separated by "and", is a binary condition, that involves columns from 2 tables. I know these conditions could involve variables from more than 2 tables, such as: select * from A join B join C on A.x+B.x+C.x<10 and A.y+B.y+C.y<100 in the "real world" applications, are tertiary and more complex join conditions common?

I know about insert ignore. That's of no use, because MariaDB does not consider NULL to be part of the uniqude index. Therefore I have to use if ... else. But this code does not run:

select *
from Nodes 
where Key = 'FAQPage';

if not found_row() then
   insert into Nodes (Key, ParentId)
   values ('FAQPage', null)
end if;

How can I use if ... else to conditionally insert data in MariaDB?

I have a situation where I need to get result based on conditional joins For example, SELECT CGI.SALES_BRAND_BSNSS_ID, CGI.SALES_BRAND_CD, NVL(MTRX. matrix_score_cd,'RV1') MATRIX_SCORE_CD, MDL.CORP_GROUP_ID, MDL.MKTBRIDGE_RUN MKTBRIDGE_RUN_CD, MDL.MKTBRIDGE_GROW MKTBRIDGE_GROW_CD, MDL.MACRO_SEG MACRO_SEG_CD FROM BIRTAB.CGI_ACTIVE_CUST_SEG_V MDL INNER JOIN BIRTAB.SBBID_CGI_HIST_XREF CGI ON MDL.CORP_GROUP_ID = CGI.CORP_GROUP_ID LEFT JOIN BIRTAB.CUST_SEG_SCORE_MATRIX MTRX ON -- if below two join conditions matches then it need to go to 3rd join condition else ignore all joins, NVL(trim(MDL.ECHECK_DECILE_CD),'%') = trim(MTRX. ECHECK_DECILE_CD) AND NVL(trim(MDL.MACRO_SEG),'%') = trim(MTRX.MACRO_SEGMENT_CD) AND -- if the above two joins matches and below join doesn't matches then ignore below two joins and get result based on above 2 joins NVL(trim(MDL.MKTBRIDGE_RUN),'%') = trim(MTRX.MKT_BRIDGE_RUN_CD) AND -- if the above three joins matches and below join doesn't matches then ignore below join and get result based on above 3 joins NVL(trim(MDL.MKTBRIDGE_GROW),'%') = trim(MTRX.MKT_BRIDGE_GROW_CD) Can anyone suggest some ideas to perform this logic. Ty

I want to select all available and bought packs. An user can open two pages to buy one pack, this results in two rows in the payment table. One gets status 'Pending' and the one that is bought gets status 'Paid'. I created the code below to show all available and paid packs for 1 user.

select distinct p.id_pack, pa.status,
	CASE
		WHEN pa.status = 'paid' THEN 'Paid'
		WHEN pa.status = 'pending' THEN 'Available'
		WHEN pa.status ISNUll THEN 'Available'
		END Status2
from packs p
left join payment pa
on p.id_pack = pa.pack
left join users u
on u.id_user = pa.user
and u.cognito_id='x'
order by p.id_pack

**Current result** | id_pack | status2 | | -------- | -------------- | | 1| Paid | | 2| Paid | | 2| Available| | 3| Paid | The third row (id_pack 2, status2 'Available') should **not** be selected because that pack has been bought by the user (That row is created because two buy pages had been opened). **Expected result** | id_pack | status2 | | -------- | -------------- | | 1| Paid | | 2| Paid | | 3| Paid | Anyone knows how to do this? **UPDATE** ---------------------------------------------------------------------------- I ran the code from jjanes. The result is under the image.

`
select p.id_pack,  u.id_user,
    max(CASE
        WHEN pa.status = 'paid' THEN 'Paid'
        WHEN pa.status  'paid' THEN 'Available'
		WHEN pa.status isnull then 'Available'
        END) as Status2
from packs p
left join payment pa
on p.id_pack = pa.pack
left join users u
on u.id_user = pa.user
and u.cognito_id='x'
group by p.id_pack, u.id_user
order by p.id_pack

` Somehow it is creating two entries for each row and it transforms 'available' to 'paid'(?). I'm a bit clueless on why this is.

I have a table comment and a table price like below. The key columns in both tables are ticker_id, price_datetime and price_open. The last column threshold in table comment (which currently NULL) is something that I need to fill in with after some calculation queries (if this is achievable). comment table: +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+ | comment_id | comment_datetime | author | comment | ticker_id | price_datetime | price_open | threshold | +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+ | 1 | 2014-09-22 06:05:00 | A1 | C1 | 343 | 2014-09-22 08:00:00 | 53.25000 | | +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+ | 2 | 2014-09-22 06:39:00 | A2 | C2 | 1 | 2014-09-22 08:00:00 | 62.00000 | | +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+ | 3 | 2014-09-22 08:13:00 | A3 | C3 | 178 | 2014-09-22 08:13:00 | 5.15000 | | +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+ price table: +----------+---------------------+------------+-----------+ | price_id | price_datetime | price_open | ticker_id | +----------+---------------------+------------+-----------+ | 1 | 2014-09-22 08:01:00 | 62.00000 | 1 | +----------+---------------------+------------+-----------+ | 2 | 2014-09-22 08:02:00 | 62.00000 | 1 | +----------+---------------------+------------+-----------+ | 3 | 2014-09-22 08:03:00 | 62.00000 | 1 | +----------+---------------------+------------+-----------+ In each row of table comment, price_open will be used as "base price". - For each row of table comment - Match the ticker_id, price_datetime and price_open with table price - Then, get the +-2 days for each price_datetime (follows with the price_open) - Then, count whether any of the price.price_open within that 5 days exceeds 5%, 10% or 15% of the "base price" - Conditions: - If any of the price.price_open within that 5 days equals/exceeds 15% of the "base price", then fill in comment.threshold with "R" - If any of the price.price_open within that 5 days equals/exceeds 10% of the "base price" (but less than 15%), then fill in comment.threshold with "A" - If any of the price.price_open within that 5 days equals/exceeds 5% of the "base price" (but less than 10%), then fill in comment.threshold with "Y" - If any of the price.price_open within that 5 days is less 5%, then fill in comment.threshold with "C" - For empty values in columns comment.price_datetime and comment.price_open, we will leave it NULL as it is, thus NULL for comment.threshold as well. Is the above going to be achievable in MySQL using JOIN? I am trying to learn about JOIN right now, unfortunately it seems way too complicated to me, I have no clue about the query that I should execute as I just started learning MySQL. I've tried explaining my question in details, but if there's anything unclear, kindly let me know. Any help would be much appreciated. Thank you. **EDIT** (as requested by Verace): CREATE statements: CREATE TABLE comment ( comment_id int(11) NOT NULL AUTO_INCREMENT, comment_datetime datetime NOT NULL, author varchar(25) NOT NULL, title varchar(250) NOT NULL, comment text NOT NULL, ticker_id int(11) NOT NULL, price_datetime datetime DEFAULT NULL, price_open decimal(12,5) DEFAULT NULL, threshold varchar(10) DEFAULT NULL, PRIMARY KEY (comment_id) ) CREATE TABLE price ( price_id int(11) NOT NULL AUTO_INCREMENT, price_open decimal(12,5) DEFAULT NULL, ticker_id int(11) NOT NULL, price_datetime datetime NOT NULL, PRIMARY KEY (price_id), UNIQUE KEY datetime (price_datetime,ticker_id) ) Expected result: +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+ | comment_id | comment_datetime | author | comment | ticker_id | price_datetime | price_open | threshold | +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+ | 1 | 2014-09-22 06:05:00 | A1 | C1 | 343 | 2014-09-22 08:00:00 | 53.25000 | C | +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+ | 2 | 2014-09-22 06:39:00 | A2 | C2 | 1 | 2014-09-22 08:00:00 | 62.00000 | Y | +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+ | 3 | 2014-09-22 08:13:00 | A3 | C3 | 178 | 2014-09-22 08:13:00 | 5.15000 | R | +------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+