Ravikumar Ravikumar - 4 months ago 12
SQL Question

Referencing column depending on Ids of another column in sequence

I am trying to update (reference) a column (oid) of one table with OID of another table's column with certain condition.

Example :

Customer Table :
------------------
CID name oid
-------------------
1 abc null
2 abc null
3 abc null
4 xyz null
--------------------

Order Table
--------------
OID name
--------------
10 abc
11 abc
12 abc
13 xyz
--------------


Ouput should be :

Customer Table :
------------------
CID name oid
-------------------
1 abc 10
2 abc 11
3 abc 12
4 xyz 13
--------------------


I have tried the following

UPDATE customer as c, order as o
SET c.oid = o.OID
WHERE c.name = o.name;
-----------------------------
update customer INNER JOIN order on customer.name=Order.name
SET customer.oid=Order.OID
where customer.oid IS null;


But the customer table is being updated as follows

Customer Table :
------------------
CID name oid
-------------------
1 abc 10
2 abc 10
3 abc 10
4 xyz 13
--------------------

Answer

The idea is to assign a row number to each of the entries in Customer table and Order table.

Thus when making an inner join between these two tables you have two conditions right now (whereas previously it was one i.e. only name).

One condition is name and another one is the row_number

You can go with this query:

UPDATE Customer CT
INNER JOIN (
    SELECT
        customerTable.CID,
        orderTable.OID FROM 
        (
            SELECT
                *, 
                @rn1 := @rn1 + 1 AS row_number
            FROM
                Customer C
            CROSS JOIN (SELECT @rn1 := 0) var
            ORDER BY CID
        ) AS customerTable
    INNER JOIN (
        SELECT
            *, 
            @rn2 := @rn2 + 1 AS row_number
        FROM
            `Order` O
        CROSS JOIN (SELECT @rn2 := 0) var
        ORDER BY OID
    ) AS orderTable ON customerTable. NAME = orderTable. NAME
    AND customerTable.row_number = orderTable.row_number
) AS combinedTable ON CT.CID = combinedTable.CID
SET CT.oid = combinedTable.OID

Note: Since joining these two tables on matching name is not sufficient for what are you looking for. That's why besides matching name assign a row_number to each of the rows (both in Customer and Order table. Then make an inner join between these two tables on matching name and row number. Thus you are restricting one entry to be joined multiple times with other entries from another table.


TEST SCHEMA & DATA:

Couldn't add an sql fiddle

DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `CID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `oid` int(11) DEFAULT NULL,
  PRIMARY KEY (`CID`)
);
INSERT INTO `customer` VALUES ('1', 'abc', null);
INSERT INTO `customer` VALUES ('2', 'abc', null);
INSERT INTO `customer` VALUES ('3', 'abc', null);
INSERT INTO `customer` VALUES ('4', 'xyz', null);

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `OID` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
);
INSERT INTO `order` VALUES ('10', 'abc');
INSERT INTO `order` VALUES ('11', 'abc');
INSERT INTO `order` VALUES ('12', 'abc');
INSERT INTO `order` VALUES ('13', 'xyz');

See now, how does the Customer table look like:

SELECT
*
FROM Customer;

Output:

CID name  oid
1   abc   10
2   abc   11
3   abc   12
4   xyz   13