Databases
StackOverflow2013
Postsdbo
POIn SQLite, how do I update a table when the PK is unqiue but the where clause is the same?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIn SQLite, how do I update a table when the PK is unqiue but the where clause is the same?
    primarykey
    data
    text
    <p>I'm provided the following table in SQLite.</p> <pre><code>CREATE TABLE Items ( PK INTEGER PRIMARY KEY AUTOINCREMENT, ItemId VARCHAR, Sold VARCHAR); </code></pre> <p>I have two rows with the following data, the PK is unique and ItemId is the same. When an Insert statement is ran, two rows are inserted with the same ItemId.</p> <blockquote> <p>1, 16, 22</p> <p>2, 16, 19</p> </blockquote> <p>To run an Update statement - I'm limited by the fact that I am only being passed the ItemId and I need to update two rows in ascending order. While I truly recognize this is a <em>horrible</em> data model, it is what I'm up against with no ability to change the table structure.</p> <p>Is there a way to update each row with the updated Sold value when I have duplicate ItemId values, but the PK is unique?</p> <p>At this point, I've tried the following, but I still update both rows with the second update statement's Sold value (in the case following, the value of 20).</p> <pre><code>UPDATE Items SET Sold = 23 WHERE ItemId = 16 AND ROWID = ( Select rowid From Items Order By ROWID Limit 1); UPDATE Items SET Sold = 20 WHERE ItemId = 16 AND ROWID = ( Select rowid From Items Order By ROWID Limit 1); </code></pre> <p>Thank you.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJuan Mellado
    1. USTodd
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat exactly is the business expecting to see at the end - how do they distinguish the 2 items sold relations?
      singulars
      1. POIn SQLite, how do I update a table when the PK is unqiue but the where clause is the same?
      1. USMark
    2. COWhat does "ascending order" mean in this case? Ascending order of primary key? And is your problem strictly limited to two rows (which is easy) or does it have to deal with any number of rows?
      singulars
      1. POIn SQLite, how do I update a table when the PK is unqiue but the where clause is the same?
      1. USLarry Lustig
    3. COMy problem is only limited to two rows each time. On the business end, they want to see two rows for the same ItemId, because the sold values just arrive as is. Therefore if the first amount sold is 23 and the second amount sold is 20, then they're inserted in ascending order, 23 then 20 - the order in which the data arrives. A third party is passing me two rows with the same ItemId each time, two 16s, two 17s, two 18s, etc. So I can have two 16s with sold values of 23, 20. I can have two 17s with sold values of 32, 48... and so on.
      singulars
      1. POIn SQLite, how do I update a table when the PK is unqiue but the where clause is the same?
      1. USTodd
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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