1

MySQL 5.6

Table: Employees

    EmployeeID   |   Name   |   PIN
    -------------+----------+---------
    1            |   Bob    |  1234
    2            |   Jim    |  3214
    3            |  Claire  |  4321
    4            | Jessica  |  NULL
    5            |  James   |  NULL
   ...           |   ...    |  ...

Table continues and all the PINs are NULL.

There's a second table: PINS

    PinID   |   PIN
    --------+--------
    1       |   1010
    2       |   1983
    3       |   5281
    4       |   6292
    5       |   9998
   ...      |   ...

Table continues on. These numbers need to be added in to the null values. There can be no duplicates and they need to be in the order that they're listed in.

Goal: Employee

    EmployeeID   |   Name   |   PIN
    -------------+----------+---------
    1            |   Bob    |  1234
    2            |   Jim    |  3214
    3            |  Claire  |  4321
    4            | Jessica  |  1010
    5            |  James   |  5281
   ...           |   ...    |  ...

I've been attempting stuff similar to this:

UPDATE Employees
SET PIN =(SELECT PIN 
          FROM PINS  
          WHERE Employees.PIN <> PINS.PIN )
WHERE PIN IS NULL;

I'm at a loss, is there any way to bring the PINS over?

EDIT: I made some progress with:

UPDATE Employees 
SET PIN = (SELECT PINS.PIN
           FROM PINS
           WHERE PINS.PinID = Employees.EmployeeID)
WHERE EXISTS ( SELECT 1
              FROM PINS
              WHERE PINS.PinID = Employees.EmployeeID)

;

however it replaces the starting PINS as well

EDIT2: added the goal table

3
  • Unless there is some other table which associates PinID to EmpoyeeID - I think you are out of luck. – Thronk Jan 12 '17 at 16:59
  • So I understand that PinID and EmployeeID are not really related, but would it be correct to say that PinID defines the order in which the PINs should be assigned and EmployeeID defines the order in which Employees' PINs should be populated? – Andriy M Jan 12 '17 at 18:11
  • Yes, exactly so. PINs are added in, in the order of PinID. There can be no duplicates or gaps between them. The Number of PINs is larger than the number of employees. Each new employee being assigned the next PIN on the list – Jason Pilipchuk Jan 12 '17 at 18:40
1

First you need to relate the first empty PIN in Employees with the first PIN in PINs, the second empty PIN with the second available PIN and so on. Then you will be able to use an update with a join and populate the rows thus related.

One way is to assign row numbers to both sets and use the row numbers as the joining criterion. As MySQL does not support the standard SQL ROW_NUMBER() analytical function, you have to do without it.

One alternative is to assign the row numbers with the help of a variable, like this:

SELECT
  s.EmployeeID,
  @r := @r + 1 AS RowNumber
FROM
  (SELECT @r := 0) AS x
  CROSS JOIN
  (
    SELECT
      EmployeeID
    FROM
      Employees
    WHERE
      PIN IS NULL
    ORDER BY
      EmployeeID ASC
  ) AS s
;

The above produces a row set containing employee IDs and corresponding row numbers. Store the resulting set as a temporary mapping table and do the same for the PINs table, unless its PinID column already is a row number column. Based on your comment to Thronk's answer that the PINs table is imported from Excel, I am going to assume that the values in PinID have indeed been generated in Excel as row numbers essentially.

So, after you save the mapping set like this:

CREATE TEMPORARY TABLE Mapping
AS
SELECT
  s.EmployeeID,
  @r := @r + 1 AS PinID
FROM
  (SELECT @r := 0) AS x
  CROSS JOIN
  (
    SELECT
      EmployeeID
    FROM
      Employees
    WHERE
      PIN IS NULL
    ORDER BY
      EmployeeID ASC
  ) AS s
;

you can use it in the UPDATE statement like this:

UPDATE
  Employees AS e
  INNER JOIN Mapping AS m ON e.EmployeeID = m.EmployeeID
  INNER JOIN PINs AS p ON m.PinID = p.PinID
SET
  e.PIN = p.PIN
;

You can play with this method at Rextester.

0
0

If the PinID is the same as the employeeID (it is in the limited example) you can use the following:

UPDATE e
SET PIN = p.PIN
FROM Employees e
JOIN PINS p ON p.PinID = e.EmployeeID
WHERE e.PIN IS NULL;
3
  • I actually edited my post a little. The IDs don't directly relate to one another – Jason Pilipchuk Jan 12 '17 at 17:06
  • then I am back to the original comment of : You are out of luck. If there is no relationship, how is the goal associated the source tables? – Thronk Jan 12 '17 at 17:45
  • The PINS table doesn't necessarily need to exist. They're being imported from an Excel spreadsheet. I thought that creating a table out of them would make the problem easier – Jason Pilipchuk Jan 12 '17 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.