Updating table from a view in sql

We will update the 2 and 5 ID from Main Table right?

because it is in Bad Table BUT 2 and 5 key is CREATE VIEW Match Table AS SELECT (Bad Table. User ID) AS "User ID", (Bad Table.key) AS "key", (SELECT value FROM Good Table WHERE Bad Table. User ID) AS "value" FROM Bad Table LEFT JOIN Good Table ON Good Table.

This gets me ALMOST there, but the problem is if it doesn't find the LIKE key specifics, it returns a NULL value and I want it to keep it's original value if it's not in Bad Table (Bad Table is essentially all of the keys that match the LIKE key specifics).

Obviously the above doesn't use Bad Table, but I thought that might help me solve this (not the case, so far! Here's a bit of an example: Main Table: ID User ID 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 Good Table: ID User ID key value 1 1 key1 good value 4 2 key1 good value Bad Table: ID User ID key value 2 1 key2 bad value 5 2 key2 bad value What I want Main Table to change to: ID User ID 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 I also thought if there was something like a VLOOKUP (like in Excel) where I could say what to do if false, but I haven't been able to work that out either.

If you have the Northwind database you can see that it has several views installed by default.

The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table.

If you want to use F5 for a single statement then you can select the statement and click F5.

HINT: CTRL Enter will execute the single statement your cursor is on.

updating table from a view in sql-43

If you always want new tabs to open, you can set a preference to pin tabs. Update that record by clicking on any of the values and changing it.

You may have heard Temporal Tables referred to as system-versioned tables.

What happens is that the historical versions of rows, according to the system time of their last modification, are stored in a separate table, while the current version resides in the original table.

UPDATE: per @Rabbit suggestion, this is the best I could come up with using the inner join (though I thought this would add to the Main Table, but I want to keep the number of rows in Main Table the same, just update that one field for the applicable rows..): I'm sure this is an awful attempt but I am certainly a novice here!

I did manage to come up with a solution (though I am sure it is highly inefficient) -- please see answer below!

Leave a Reply