CODEWITHSUNDEEP
sqlms-access
ErichO
ErichO

Reputation: 81

Change value in row based on a lookup from other rows

I have this data in a table(actually output of a query):

+--------------+------+---------+
|  Connection  | Pin  | Circuit |
+--------------+------+---------+
| Value 1      | 1    |      33 |
| Value 1      | 2    |    1004 |
| Value 1      | 3    |    1015 |
| Value 1      | 4    |         |
| Value 2      | SP-A |    1003 |
| Value 2      | SP-A |    1004 |
| Value 2      | SP-A |    1005 |
| Value 2      | SP-B |    1014 |
| Value 2      | SP-B |    1015 |
| Value 2      | SP-B |    1016 |
+--------------+------+---------+

I would like to use an SQL query to change it to this: (changing the Pin based on a matching Circuit)

e.g.: For each "SP-A", get the list of possible Circuits (1003, 1004, 1005)

+------------+-------+---------+
| Connection | Pin   | Circuit |
+------------+-------+---------+
| Value 1    |   1   |      33 |
| Value 1    |   2   |    1004 |
| Value 1    |   3   |    1015 |
| Value 1    |   4   |         |
| Value 2    |   *2* |    1003 |
| Value 2    |   *2* |   *1004*|
| Value 2    |   *2* |    1005 |
| Value 2    |   *3* |    1014 |
| Value 2    |   *3* |   *1015*|
| Value 2    |   *3* |    1016 |
+------------+-------+---------+

My SQL skills are lacking. I'm doing this in MS-Access.

Upvotes: 0

Views: 69

Answers (2)

Saeid Amini
Saeid Amini

Reputation: 1307

First of all, try this, if it works, apply it on your main data.

CREATE TABLE #TEMP
       (Connection  nvarchar(50),
        Pin nvarchar(50),
        Circuit nvarchar(50))


INSERT INTO #TEMP
SELECT Connection, Pin, Circuit FROM Table_1



UPDATE TU
SET Pin = (
    SELECT '*' + T1.Pin + '*' FROM Table_1 T1 
        INNER JOIN Table_1 T2 ON T1.Circuit = T2.Circuit AND T1.Connection <> T2.Connection AND T2.Pin = TU.Pin
)
FROM #TEMP TU
WHERE Connection = 'Value 2'

UPDATE TU
SET Circuit = '*' + T2.Circuit + '*'
FROM #TEMP TU
INNER JOIN #TEMP T2 ON TU.Circuit = T2.Circuit AND TU.Connection <> T2.Connection
WHERE TU.Connection = 'Value 2'

SELECT * FROM #TEMP

DROP TABLE #TEMP

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can express the logic using a correlated subquery:

update t
    set pin = (select top (1) t2.pin
               from t as t2
               where t2.circuit = t.circuit and
                     t2.connection <> t.connection
              )
    where pin in ('SP-A', 'SP-B');

Upvotes: 0

Related Questions