Check duplication of two MySQL tables

How do I prevent the driver and vehicle data from being duplicated?
when the data has been used by the driver, the data cannot be used by the vehicle and vice versa. when checking is complete then just insert data into MySQL.

Which should have a constraint, which in turn should produce an error if they exist.

But you could also first perform a select driver_id from drivers where driver_id = yourpayload if no results, continue the flow.

can you give an example of the function program?

Node-red does not know if there are duplicates in the database, first query the database, if there are no results, continue.


Hi bakman,

can you export an example?
I did it like the picture below, and still confused about how to do it

First make sure you understand how to do it in mysql, which is nothing to do with node red. Once you understand that then experiment with the mysql node so you understand how to use it. Then try to solve the problem.
I thought mysql had an Insert Or Update command which might solve your problem, so check that out first.

1 Like

... search for UPSERT...

I've never heard of UPSERT, so I did ...

But I don't think it's applicable to this case where @afafirmansyah wants to INSERT INTO drivers only if there is no matching record in vehicles.

You don't say what database engine it is but you may be able to do something like this (which works for me in MySQL)

INSERT INTO drivers (driver_id, driver_name)
SELECT a.driver_id, a.driver_name
FROM (SELECT 'abc123' as driver_id, 'Bijul Kaur' as driver_name) a
WHERE NOT EXISTS (SELECT 1 FROM vehicles b WHERE b.vehicle_id = 'abc123');

However, you presumably want to inform the user that the drivers record was not created. The simplest way to achieve that is a seperate SELECT query as @bakman2 suggests.

Can you explain what you mean by that please? Do you mean that both vehicle and driver ids must come from one pool of ids? So a driver cannot have the same id as a vehicle? If that is the case then I would put them in one table, with a field to identify whether this is a driver or a vehicle.

thanks for the help, I have solved the above problem after getting inspired by bakman2's answer.

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.