How can I use data from one table to create new rows in a second table when the two tables don'-CodePudding

I have a table called leads and a table called call_logs, each with various columns but only two columns from leads and three columns from call_logs are relevant.

I need to insert new rows in the call_logs table for each row in the leads table where the owner is "12345". The "owner" column values in the leads table need to be entered into the "user" column of the call_logs table. Likewise, the "lead_id" column values in the leads table need to be entered into the "lead" column of the call_logs table.

Lastly I need to update the call_started value for each newly created row with today's date.

So the leads table data:

owner	lead_id	first_name	last_name
12345	54321	John	Smith
43212	43928	Jane	Doe

Should insert into the call_logs table:

user	lead	call_started	call_id
12345	54321	2022-05-02 10:50:43
82919	73261	2022-04-21 09:32:12	"this is pre-existing data"

I have tried variations of:

UPDATE call_logs cl 
JOIN leads l ON l.owner = cl.user 
SET cl.call_started = CURDATE(), cl.user = 12345 
WHERE l.owner = 12345

UPDATE call_logs
LEFT JOIN leads ON call_logs.user = leads.owner AND call_logs.lead = leads.lead_id
SET call_started = CURDATE()

In this snippet I attempted to abandon the UPDATE JOIN idea and just create new rows and then update the call_started value in a separate snippet.

INSERT INTO call_logs (user, lead)  
SELECT owner, lead_id
  FROM leads
 WHERE owner = 12345

The first two sql snippets ran but did not change anything and the last snippet gave me an error stating

Field 'call_sid' doesn't have a default value

The call_sid field is the actual first column in the call_logs table but is irrelevant for my purposes. Any other fields in the newly created rows in the call_logs table can have null values and it will not affect what I'm trying to do.

CodePudding user response：

You can do 2 different things:

Set a default value for the column call_sid. This way you don´t need to change your query.
If you don´t want to set a default value, and if that value it is not important (be carefull with that, it may be important for somer other process), then you can change your query to:
```
INSERT INTO call_logs (user, lead, call_sid)  
SELECT owner, lead_id, 0
FROM leads
WHERE owner = 12345
```