How to get value from other table conditionally

Transact-SQL
1

I use Sqlite in my project. I have the following database:

CREATE TABLE [Customers](
  [CustomerID] INTEGER PRIMARY KEY AUTOINCREMENT, 
  [Name] TEXT, 
  [Product] TEXT);
CREATE TABLE [Products](
  [ProductID] INTEGER PRIMARY KEY AUTOINCREMENT, 
  [Category] TEXT UNIQUE, 
  [ProductName] TEXT UNIQUE, 
  [Count] INT);

INSERT INTO Products (Category, ProductName, Count)
VALUES ('TV', 'SONY BRAVIA A8H', 29),
('MotherBoard', 'ASUS Prime h510m-k', 18),
('Monitor', 'ASUS va27ehe', 21),
('LTE Modem', 'UTEL L443', 9);

INSERT INTO Customers (Name, Product)
VALUES ('Alex', 'ASUS va27ehe'),
('Alex', 'UTEL L443'),
('Amanda', 'ASUS va27ehe'),
('Amanda', 'GIGABYTE'),
('Amanda', 'SONY BRAVIA A8H');

I want to write a SQL query to check "Product" column in "Customers" table and if there were matching item, replace the corresponding "ProductID" in the "Products" table, and if there were no matching, delete that row.

How can I do that?

2

this forum is on SQL Server forums. You might not get much help on question related to SQLite.

3

You want to INNER JOIN the two tables.