Advanced MySql Query: Update table with info from another table

Question

I would like to update a table in mySql with data from another table.

I have two tables "people" and "business". The people table is linked to the business table by a column called "business_id".

The necessary table structure, primary key is starred (Table: columns): People: *business_id, *sort_order, email Business: *business_id, email

I would like to update the business table email column with the email from the people table, something like this (I know I am missing something here):

UPDATE business b SET email = (SELECT email  from People p where p.business_id = b.business_id AND sort_order = '1') WHERE b.email = '';

Does this make sense? Is it possible?

score 117 · Accepted Answer · answered Jul 29 '09 at 18:19

117

UPDATE business b, people p
   SET b.email = p.email
 WHERE b.business_id = p.business_id
   AND p.sort_order = '1'
   AND b.email = ''

answered Jul 29 '09 at 18:19

ChssPly76

99,456
24
206
195

very nice but from my side it takes up to 3 secs – NineCattoRules Feb 28 '16 at 23:35

score 16 · Answer 2 · answered Jul 29 '09 at 19:34

16

Note, if sort_order is an INT, then don't use '1' - use 1:

UPDATE business b
JOIN People p
ON p.business_id = b.business_id
AND p.sort_order = '1'
SET b.email = p.email
WHERE b.email = '';

answered Jul 29 '09 at 19:34

hobodave

28,925
4
72
77

score 0 · Answer 3 · answered Jun 01 '18 at 17:59

0

Try this, it works fine for me.

Update table a, table b
Set a.importantField = b.importantField,
a.importantField2 = b.importantField2
where a.matchedfield = b.matchedfield;

answered Jun 01 '18 at 17:59

king zecole

59
2

Generally, answers are much more helpful if they include an explanation of what the code is intended to do, and why that solves the problem without introducing others. – Tim Diekmann Jun 01 '18 at 18:17

Advanced MySql Query: Update table with info from another table

3 Answers3

Linked

Related