Is there a way to check if lead_id is multiple times in the table, and if yes, exclude from query? The reason is that I only want to add new rows if the status_id = 1 AND if there are no other status_id/rows found for that lead_id.
The database is designed in a way that for each status update it creates a new row. So the old row has an older timestamp and remains in the DB table, and these will be shown on the front-end in a status history component. But since it is designed this way, I can't simply say: give every user with status 1, a new status update. Because then it will add a row for every lead_id, since all leads will enter the DB with status_id 1.
Query I was using so far:
INSERT INTO `lead_has_status` (`lead_id`, `lead__status_id`, `comment`, `created_at`, `updated_at`)
SELECT (`lead_id`, 13, "", `created_at`, `updated_at`)
WHERE `lead__status_id` = 1 AND
What the data looks like:
| lead_id | status_id | moredata |
|---|---|---|
| 1 | 1 | data |
| 1 | 12 | data |
| 2 | 1 | data |
| 2 | 14 | data |
| 3 | 1 | data |
| 4 | 1 | data |
| 5 | 1 | data |
| 6 | 1 | data |
For every new lead (form submission) it will create a row and gives the lead a status_id of 1 (status: new). I do need to bulk update 200+ leads from 1 to another status_id, but with the query I have right now it will update every single row containing the status_id of 1. What I want is that it will first check if the lead_id is only there in the table once, and if true, add a new row with the updated status_id.
So after the sql query it will need to look like this:
| lead_id | status_id | moredata |
|---|---|---|
| 1 | 1 | data |
| 1 | 12 | data |
| 2 | 1 | data |
| 2 | 14 | data |
| 3 | 1 | data |
| 3 | 12 | data |
| 4 | 1 | data |
| 4 | 12 | data |
| 5 | 1 | data |
| 5 | 12 | data |
| 6 | 1 | data |
| 6 | 12 | data |
TLDR: If lead_id is only found once in the table, add new row with status_id.