Insert Value into Table If Conidition Is Met At Least One Time

Insert value into table if conidition is met at least one time

idCustomer column in both table, so add the table name in select query.

INSERT INTO test.specialcustomers
(idCustomer, CustomerName)
SELECT test.customer.idCustomer, CustomerName
FROM test.customer
LEFT JOIN test.orders ON test.customer.idCustomer = test.orders.idCustomer
WHERE PaymentMethod ="PayPal";

MySQL insert only if a condition is true

Select the count into a variable and then use that in the comparison.

DECLARE total_row INT DEFAULT 0

START TRANSACTION;

INSERT INTO fields (field_name, control_type_id, needs_approval)
VALUES ('Array Photos', 3, 0);


SELECT count(job_type_name) FROM job_types WHERE job_type_name = 'Cash' INTO total_rows;

IF total_rows =2 THEN
ROLLBACK;
ELSE

INSERT INTO field_to_job_type (field_id, job_type_id, sequence_number, parent_id)
VALUES (last_insert_id(), (SELECT job_type_id FROM job_types WHERE job_type_name = 'Cash'), 1, (SELECT field_id FROM fields where field_name = 'Photo Pack'));
COMMIT;
END IF;

List all values if value exists multiple times and if one condition per criteria is met

Assuming suppliers are unique for a campaign, then window functions are a simpler solution:

SELECT o.*
FROM (SELECT o.*,
COUNT(*) OVER (PARTITION BY campaign) as num_supplier,
SUM(supplier_rating = 'low') OVER (PARTITION BY campaign) as num_low
FROM operations o
) o
WHERE num_supplier > 1 AND num_low > 0;

Or you can use aggregation to define the campaigns and then use IN, EXISTS, or JOIN to get the original rows:

SELECT o.*
FROM operations o
WHERE o.campaign IN (SELECT o2.campaign
FROM operations o2
GROUP BY o2.campaign
HAVING COUNT(*) > 1 AND SUM(supplier_rating = 'low') > 0
);

Here is a db<>fiddle.

Identify if at least one row with given condition exists

Commonly, you'd express this as either

SELECT COUNT(*)
FROM employee
WHERE name like 'kaushik%'
AND rownum = 1

where the rownum = 1 predicate allows Oracle to stop looking as soon as it finds the first matching row or

SELECT 1
FROM dual
WHERE EXISTS( SELECT 1
FROM employee
WHERE name like 'kaushik%' )

where the EXISTS clause allows Oracle to stop looking as soon as it finds the first matching row.

The first approach is a bit more compact but, to my eye, the second approach is a bit more clear since you really are looking to determine whether a particular row exists rather than trying to count something. But the first approach is pretty easy to understand as well.

ON DUPLICATE KEY UPDATE - Condition WHERE vs CASE WHEN vs IF?

You can use simple IF function in value like this:

INSERT INTO ... ON KEY DUPLICATE UPDATE
name = VALUES(name),
number = VALUES(number),
timestamp = IF(timestamp > VALUES(timestamp), VALUES(timestamp), timestamp)

If condition is not met, it will update timestamp with the same timestamp which already exists. It does not matter, because update to same values is optimized before it is even executed, so MySQL will not make real update. You should not afraid of some performance penalty.

EDIT:
IF works likes this:

IF(condition, returned when true, returned when false)

Maybe you need to switch those two arguments to fit your condition like you want.

Select records in on table based on conditions from another table?

I will do it in this way (it should be valid in most database systems:

select *
from b
where type_id in (
select type_id
from a
where status = true
)

To your question about if yours is a good way, my answer is no, it is not a good way because it likely forces a big intermediate record set (by the joining) then a time consuming distinct on the intermediate record set.

UPDATE

After some thought I realized there is no absolute good or bad solution. It all depends on the data your have in each table (total records, value distribution, etc...). So go ahead with the one that is clearly communicate the intention and be prepared to try different ones when you hit a performance issue in production.

Aggregate rows if one row meets a specific condition

One way to do this is to use a conditional expression together with a having clause like this:

select name, sum(isOk) ok_sum
from your_table
group by name
having sum(case when day = 2 and isOK = 1 then 1 else 0 end) > 0;

With your sample data the result would be:

name    ok_sum
Flo 2
Tim 1

As MySQL evaluates boolean expressions as 1 or 0 it should be possible to reduce the condition to this:

having sum(day = 2 and isOK = 1) > 0;

Another way to do it would be to use a correlated subquery that makes sure there exists a row with Day = 2 and isOk = 1 for the Name:

select t1.name, sum(t1.isOk) ok_sum
from your_table t1
where exists (
select 1
from your_table t2
where t2.day = 2 and t2.isOK = 1 and t1.name = t2.name
)
group by t1.name
  • See this fiddle

SQL Select all rows per group if a condition is met at least once

Select *
From yourtable
where id in (
select distinct id
from yourtable
where v2 = 3
)


Related Topics



Leave a reply



Submit