Help with an Update SQL query

[chrislynch8]

Hi,

I want to update my contacts table and set the team_id of all contacts to the team of there Parent Account. How would this be acheived with a SQL command I tried the following but that returns errors.

But you cannot run the UPDATE statement where my selection of team_id from accounts returns mutilpe rows


Rgds
Chris

[davidboris]

Hello,

I guess this is the one,

 UPDATE contacts c 
INNER JOIN accounts_contacts ac ON ac.contact_id = c.id AND ac.deleted = 0 
INNER JOIN accounts a ON ac.account_id = a.id AND a.deleted = 0 
SET c.team_id = a.team_id, c.team_set_id = a.team_set_id AND c.deleted = 0; 
  

[chrislynch8]

Thanks,

Sorry that didn't work, seems to have set all my team_id in Contacts where they have a parent Account to 0

Warning Code : 1292
Truncated incorrect DOUBLE value: 'a1a808dc-49a6-102c-8082-001d0911a977 '

Any idea what this is

#######################

should be where c.deleted=0

[MissAran]

I believe it's your AND within the SET arguement. I took the AND out and it updated my contacts.

UPDATE contacts c
INNER JOIN accounts_contacts ac ON ac.contact_id = c.id AND ac.deleted = 0
INNER JOIN accounts a ON ac.account_id = a.id AND a.deleted = 0
SET c.team_id = a.team_id, c.team_set_id = a.team_set_id, c.deleted = 0;

Updated version of davidboris.

Tried and tested.

But, the results are if the contact is deleted it will be made undeleted with that c.deleted = 0. I guess you don't want that.

UPDATE contacts c
INNER JOIN accounts_contacts ac ON ac.contact_id = c.id AND ac.deleted = 0
INNER JOIN accounts a ON ac.account_id = a.id AND a.deleted = 0
SET c.team_id = a.team_id, c.team_set_id = a.team_set_id;

[davidboris]

Hello,

Excuse me for the wrong query,

 UPDATE contacts c 
INNER JOIN accounts_contacts ac ON ac.contact_id = c.id AND ac.deleted = 0 
INNER JOIN accounts a ON ac.account_id = a.id AND a.deleted = 0 
SET c.team_id = a.team_id, c.team_set_id = a.team_set_id WHERE c.deleted = 0;