CODEWITHSUNDEEP
mysqlsqlsql-update
Sanchit
Sanchit

Reputation: 727

MySQL query, finding from one table and insert into second table

I am looking for the following:

enter image description here

Can someone help me with it? Database is MySQL.

Upvotes: 1

Views: 92

Answers (3)

Sepster
Sepster

Reputation: 4849

Use this to confirm you're getting the rows you're expecting (ie BEFORE updating anything):

SELECT
  t1.Specified

FROM
  table2 t2

  INNER JOIN table1 t1
  ON t1.device = t2.device
  AND LEFT(t1.interface,3) = LEFT(t2.interface,3)

And then, assuming that's right:

  UPDATE table2 t2

  INNER JOIN table1 t1
  ON t1.device = t2.device
  AND LEFT(t1.interface,3) = LEFT(t2.interface,3)

  SET t2.Avgin = ifnull(t1.specified,'Default Value For When t1.Specified is NULL')

Note we're using an INNER join... that means that rows from table2 that have no corresponding row in table1, are discarded from the results (which is what you want).

The IFNULL will allow you to use a default value in the case when your join succeeds (because device and first three chars of interface are common to both tables), but table1.specified has a NULL value for that row.

Upvotes: 1

nl-x
nl-x

Reputation: 11832

If there are more than one matches, only the first one will be used. If there are none, null will be used. If you would like something else, use ifnull().

UPDATE
  table2
SET
  avgin=ifnull(
    (
      SELECT
        Specified
      FROM
        table1
      WHERE
        table1.Device=table2.Device
        AND substring(table1.Interface,1,3)=substring(table2.Interface,1,3)
      LIMIT 1
    ),
    'default value'
  )

edit: added the ifnull()

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

UPDATE with JOIN is what you need here, something like this:

UPDATE Table2 AS t2
INNER JOIN table1 AS t1  ON LEFT(t2.Interface, 3) = LEFT(t1.Interface, 3)
                        AND t1.Device             = t2.Device
SET t2.Avgin = t1.specified;

With the JOIN condition, as you explained in your question:

 LEFT(t2.Interface, 3) = LEFT(t1.Interface, 3)
 AND 
 t1.Device             = t2.Device

LEFT will give you the first 3 chars from the left of both table.

See it in action here:

This will make the table2 looks like:

|         CID |       DEVICE | INTERFACE |  AVGIN |
---------------------------------------------------
| HDC-HKG-R01 | HDC-TBONE-P1 |   P09/0/0 | 121.36 |
| OCB-OCD-R01 |      OCB-PE1 |     Gi5/2 |   0.17 |
| HDC-BSA-R01 | HDC-TBONE-P1 |   Se9/2/0 | (null) |

Upvotes: 1

Related Questions