SingleStore provide multiple ways to Update data in a table using another table.

SingleStore: Update Using Another Table

There are many scenarios where it is required to update a table either directly or using another table.

➠ Sample Table and Data

Creating Tables: Creating 2 tables with similar structure to understand the logic in details.

Table 1
CREATE ROWSTORE TEMPORARY TABLE updatetable(
    id INTEGER,
    name VARCHAR(100),
    indicator_flag CHAR(1),
    SHARD KEY(id)    
);


Table 2
CREATE ROWSTORE TEMPORARY TABLE othertable(
    id INTEGER,
    name VARCHAR(100),
    indicator_flag CHAR(1),
    SHARD KEY(id)
);


Populating Tables: Inserting relevant data in newly created tables for understanding logic.

Table 1:
INSERT INTO updatetable VALUES(1,'SingleStore',null);
INSERT INTO updatetable VALUES(2,'Database',null);
INSERT INTO updatetable VALUES(3,'Oracle',null);
INSERT INTO updatetable VALUES(4,'Vertica',null);

Table 2:
INSERT INTO othertable VALUES(1,'SingleStore','Y');
INSERT INTO othertable VALUES(2,'Database','N');
INSERT INTO othertable VALUES(5,'DB2','Y');
INSERT INTO othertable VALUES(6,'MYSQL','Y');

➠ Basic update : Update hardcode values to a table

UPDATE updatetable 
SET indicator_flag='Y'
WHERE id=4;

Output:
Rows matched: 1  Changed: 1  Warnings: 0.

➠ Multicolumn update : Update multiple column of a table

UPDATE updatetable 
SET indicator_flag='Y',
    name='Hive'
WHERE id=4;

Output:
Rows matched: 1  Changed: 1  Warnings: 0

➠ Different approaches can be used to update data from one table using other table.

Approach 1 : update with the help of other table using join filter.

UPDATE updatetable , othertable
SET indicator_flag = othertable.indicator_flag
WHERE updatetable.id = othertable.id;


Output:
Rows matched: 2  Changed: 2  Warnings: 0


Approach 2 : update with the help of other table using join filter and table Alias.

UPDATE updatetable , othertable AS tempTableAlias
SET indicator_flag = tempTableAlias.indicator_flag
WHERE updatetable.id = tempTableAlias.id;


Output:
Rows matched: 2  Changed: 2  Warnings: 0


Note: Merge is not supported in SingleStore as of now.