CODEWITHSUNDEEP
mysql
user4014674
user4014674

Reputation:

update all fields from select from 2 tables

providers:

SELECT ID,has_account from service_providers where has_account='yes';

+-----+-------------+
| ID  | has_account |
+-----+-------------+
|  17 | yes         |
| 687 | yes         |
| 158 | yes         |
| 292 | yes         |
| 306 | yes         |
| 444 | yes         |
| 536 | yes         |
| 572 | yes         |
| 601 | yes         |
| 697 | yes         |
+-----+-------------+

accounts:

SELECT ID,active from accounts;

+-----+--------+
| ID  | active |
+-----+--------+
|   1 | yes    |
|   3 | yes    |
|   4 | yes    |
|   7 | yes    |
|   8 | yes    |
|   9 | yes    |
|  10 | yes    |
|  11 | yes    |
|  12 | yes    |
|  13 | no     |
|  14 | yes    |
|  15 | yes    |
|  16 | no     |
+-----+--------+

i want this in mysql:

if account == active
set provider.has_account == 'yes'

i need to set the has_account field to yes id the account is account is active.

For the sake of privacy, lets just say the tables are connected by the ID

Upvotes: 0

Views: 26

Answers (1)

Barmar
Barmar

Reputation: 780869

Join the tables and set the field with an UPDATE statement:

UPDATE providers AS p
JOIN accounts AS a ON p.id = a.id
SET p.has_account = 'yes'
WHERE a.active = 'yes'

Upvotes: 1

Related Questions