StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQLite alias of table name in UPDATE statement
text
Body
copied!<p>I have a SQLite table called Price like this:</p> <pre><code>date, productId, price ---------------------- 20120601, 10 , 10.1 20120601, 20 , 20.1 20120602, 10 , 0 20120602, 20 , 0 20120602, 30 , 0 </code></pre> <p>The table is created with this statement:</p> <pre><code>CREATE TABLE "Price" ("date" INTEGER NOT NULL , "productId" INTEGER NOT NULL , "price" DOUBLE, PRIMARY KEY ("date", "productId")) </code></pre> <p>I want to fill the price of date==20120602 with the price of the same product in the previous date.</p> <p>i.e I want the table turn into like this:</p> <pre><code>date, productId, price ---------------------- 20120601, 10 , 10.1 20120601, 20 , 20.1 20120602, 10 , 10.1 20120602, 20 , 20.1 20120602, 30 , 0 </code></pre> <p>So I tried this SQL statement:</p> <pre><code>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) </code></pre> <p>But SQLite gave me an error near dot.</p> <p>Than I tried this statement:</p> <pre><code>UPDATE New Set New.Price = Old.Price FROM Price New, Price Old WHRER ...... </code></pre> <p>SQLite gave me an error near FROM.</p> <p>I double checked SQLite's SQL Syntax but found no lucky. Even I never tried so, I think both statements would work as expected in MS SQL Server. The first statement might work if New and Old are too different tables, but I need them in same table.</p> <p>I really don't want to write a for loop to update once a line in my C++ code, I need advice on the right way to achieve this in SQLite.</p> <p>Thank you.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload