Alias ​​SQLite table name in UPDATE statement

Question

I have an SQLite table called Price similar to this:

date, productId, price ---------------------- 20120601, 10 , 10.1 20120601, 20 , 20.1 20120602, 10 , 0 20120602, 20 , 0 20120602, 30 , 0 

A table is created using this statement:

 CREATE TABLE "Price" ("date" INTEGER NOT NULL , "productId" INTEGER NOT NULL , "price" DOUBLE, PRIMARY KEY ("date", "productId")) 

I want to fill in the price of the date == 20120602 with the price of the same product on the previous date.

ie I want the table to be included like this:

 date, productId, price ---------------------- 20120601, 10 , 10.1 20120601, 20 , 20.1 20120602, 10 , 10.1 20120602, 20 , 20.1 20120602, 30 , 0 

So, I tried this SQL statement:

 UPDATE Price New SET New.price = (SELECT old.price FROM Price Old WHERE New.date == 2 AND Old.date == 1 AND New.productId == Old.productId) 

But SQLite gave me an error near the point.

Than I tried this statement:

 UPDATE New Set New.Price = Old.Price FROM Price New, Price Old WHRER ...... 

SQLite gave me an error near FROM.

I double-checked the syntax of SQLite SQL, but did not find any luck. Even I have never tried so, I think that both statements will work as expected in MS SQL Server. The first statement may work if New and Old are too different tables, but I need them in the same table.

I really don't want to write a for loop to update after a line in my C ++ code, I need advice on the right way to achieve this in SQLite.

Thanks.

Answer 1

I myself experienced this. Just delete the word New after UPDATE Price and wherever you use New.something change to Price.something . I am sure there is a much more pleasant solution, but it works.

updated by code snippet from comments ...

 UPDATE Price SET price = (SELECT old.price FROM Price Old WHERE Old.date = 1 AND price.productId = Old.productId) WHERE price.date = 2