CODEWITHSUNDEEP
mysqlsql
James Stafford
James Stafford

Reputation: 1054

Update rows based off of data from another table

I have a table which keeps schedule records - our scheduler uses a specific set of colors to show the current status of appointments which is saved in a table in MySQL - more data regarding these appointments is saved in a separate table in mysql - (this is the only way this database will work because some appointments will not be scheduled at all times in which case they will exist in the "calls" table and not in the "appointments" table

Table design:

appointments
Uniqueid | Label | CustomField1
    1    |   1   |      22
    2    |   1   |      31
    3    |   1   |      76

calls
RID | Rentry | Rdisp | Rconf | Renrt | Ronsc | Rtrans | Rdest | Rclr
 22 |  <dt>  |  <dt> |  NULL | NULL  |  NULL  |  NULL |  NULL | NULL
 31 |  <dt>  |  <dt> |  <dt> | NULL  |  NULL  |  NULL |  NULL | NULL
 50 |  <dt>  |  NULL |  NULL | NULL  |  NULL  |  NULL |  NULL | NULL
 76 |  <dt>  |  <dt> |  <dt> | <dt>  |  <dt>  |  NULL |  NULL | NULL

CustomField1 represents the RID of the same row in "calls". I need to run an update statement that updates "Label" based off if there is a value in Rdisp | Rconf | Renrt | Ronsc | Rtrans | Rdest | Rclr.

Essentially running off the following pattern:

not null | Label value
 Rdisp   |    0
 Rconf   |    1
 Renrt   |    2
 Ronsc   |    3 
 Rtrans  |    4
 Rdest   |    5
 Rclr    |    6

in a select statement I would left join but I am not sure if that is possible in an update statement

so essentially 

UPDATE `appointments` SET `Label` = 0 WHERE `Rdisp` IS NOT NULL;
UPDATE `appointments` SET `Label` = 1 WHERE `Rconf` IS NOT NULL;
UPDATE `appointments` SET `Label` = 2 WHERE `Renrt` IS NOT NULL;
UPDATE `appointments` SET `Label` = 3 WHERE `Ronsc` IS NOT NULL;
UPDATE `appointments` SET `Label` = 4 WHERE `Rtrans` IS NOT NULL;
UPDATE `appointments` SET `Label` = 5 WHERE `Rdest` IS NOT NULL;
UPDATE `appointments` SET `Label` = 6 WHERE `Rclr` IS NOT NULL;

but we would have to join/link appointments.customField1 to calls.RID

New join statement:

UPDATE appointments a JOIN calls c 
ON a.CustomField1 = c.RID
SET a.Label = 0
WHERE `Rdisp` IS NOT NULL

and preferably make this transaction one process.

How would I link these tables in this situation and is it possible to make this one statement

Upvotes: 1

Views: 60

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53870

In addition to using JOIN in an UPDATE statement, you can also use CASE:

UPDATE appointments a
JOIN calls c 
ON c.RID = a.CustomField1
SET a.Label = CASE
WHEN c.Rclr IS NOT NULL THEN 6
WHEN c.Rdest IS NOT NULL THEN 5
WHEN c.Rtrans IS NOT NULL THEN 4
WHEN c.Ronsc IS NOT NULL THEN 3
WHEN c.Renrt IS NOT NULL THEN 2
WHEN c.Rconf IS NOT NULL THEN 1
WHEN c.Rdisp IS NOT NULL THEN 0
END

You also may want an ELSE statement there for a default value.

Upvotes: 2

Related Questions