SQL to update selected fields in a table from a view

Question

I am new to this, so I apologize in advance for any confusion / disappointment. I appreciate any help I can get!

I have a table ( MainTable ) in which I created two views using GoodTable and BadTable ).

• Each table has 4 columns (ID, UserID, key, value).
• An identifier is a primary key, but
• A user identifier can be repeated in several lines.

What I need to do in the Home table is to find the identifiers that are in the BAD table and update the values ​​from the GOOD value column based on the UserID and LIKE matches with the key column in the MAIN table .

Hope this makes sense.

I tried:

UPDATE MainTable
SET value = (SELECT value FROM GoodTable
WHERE MainTable.UserID = GoodTable.UserID
AND MainTable.key LIKE "some%key%specifics");

This calls me ALMOST, but the problem is that if it does not find the specific features of LIKE, it returns NULL, and I want it to keep its original value if it is not in BadTable (BadTable is essentially all keys that comply with LIKE specifications). Obviously, the above does not use BadTable, but I thought it might help me solve this problem (not so, so far!) ...

Here is a little example:

MainTable:
ID    UserID    key    value
1     1         key1   good value
2     1         key2   bad value
3     1         key3   unrelated value
4     2         key1   good value
5     2         key2   bad value
6     2         key3   unrelated value

GoodTable:
ID    UserID    key    value
1     1         key1   good value
4     2         key1   good value

BadTable:
ID    UserID    key    value
2     1         key2   bad value
5     2         key2   bad value

What I want MainTable to change to:
ID    UserID    key    value
1     1         key1   good value
2     1         key2   good value
3     1         key3   unrelated value
4     2         key1   good value
5     2         key2   good value
6     2         key3   unrelated value

, - VLOOKUP (, Excel), , , , . , :)

, , , , MySQL...

, , , , !

UPDATE: @Rabbit, , ( , MainTable, MainTable , ..):

UPDATE MainTable
JOIN GoodTable ON MainTable.ID = GoodTable.ID
SET value = (SELECT value FROM GoodTable
WHERE MainTable.UserID = GoodTable.UserID
AND MainTable.key LIKE "some%key%specifics");

, , , , !

( , ) - . ! ( @DBug @Rabbit , !)

Answer 1

: " - , BAD, GOOD, LIKE MAIN.", ...

2 5 MainTable? BadTable 2 5 - 2, 2 GoodTable.

. . ,

UPDATE MainTable m 
INNER JOIN BadTable b ON m.id = b.id 
LEFT JOIN GoodTable g ON b.UserID = g.UserID 
SET m.value = g.value

. ,

Answer 2

Coalesce, , , .

UPDATE MainTable 
SET value = (SELECT COALESCE(GoodTable.value, MainTable.value) FROM GoodTable
WHERE MainTable.UserID = GoodTable.UserID
AND MainTable.key LIKE "some%key%specifics");

GoodTable.value, NULL MainTable.value, .

Answer 3

, UPDATE.

UPDATE Table1
INNER JOIN Table2 ON Table1.FieldName = Table2.FieldName
SET ....
WHERE ....

Answer 4

, (MatchTable):

UPDATE MainTable
SET value = COALESCE((SELECT value FROM MatchTable
WHERE MainTable.ID = MatchTable.ID
AND MainTable.key LIKE "some%key%specifics"),(SELECT value));

MatchTable BAD ( BadTable) GOOD GoodTable. , :

CREATE VIEW MatchTable AS
SELECT (BadTable.ID) AS "ID", (BadTable.UserID) AS "UserID", (BadTable.key) AS "key", (SELECT value FROM GoodTable
WHERE BadTable.UserID = GoodTable.UserID) AS "value" 
FROM BadTable
LEFT JOIN GoodTable
ON GoodTable.ID = BadTable.ID;

MatchTable:

MainTable:
ID    UserID    key    value
2     1         key2   good value
5     2         key2   good value

, - , , GoodTable BadTable, :

CREATE VIEW GoodTable AS
SELECT * FROM MainTable
WHERE key = "specific_key_term";

CREATE VIEW BadTable AS
SELECT * FROM MainTable
WHERE key LIKE "some%key%specifics";

, , , MainTable ( ):

• GoodTable
• BadTable
• MatchTable.
• MainTable
• ! ( - , / !) , !

(Thanks again @DBug for being on the right track for the update part and @Rabbit for getting me in MatchTable!)