在SQLite中，当PK不同但where子句相同时如何更新表？

发布于 2024-10-13 08:19:31 字数 745 浏览 1 评论 0原文

我在 SQLite 中提供了下表。

CREATE TABLE Items ( PK INTEGER PRIMARY KEY AUTOINCREMENT, ItemId VARCHAR, Sold VARCHAR);

我有两行包含以下数据，PK 是唯一的，ItemId 是相同的。运行 Insert 语句时，将插入具有相同 ItemId 的两行。

1、16、22
2、16、19

要运行 Update 语句 - 我受到以下事实的限制：我仅传递 ItemId 并且需要按升序更新两行。虽然我确实认识到这是一个可怕的数据模型，但这正是我所面临的，因为我无法更改表结构。

当我有重复的 ItemId 值但 PK 是唯一的时，有没有办法用更新的 Sold 值更新每一行？

此时，我已尝试以下操作，但仍然使用第二个更新语句的 Sold 值（在以下情况中为值 20）更新两行。

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);

谢谢。

原文

I'm provided the following table in SQLite.

CREATE TABLE Items ( PK INTEGER PRIMARY KEY AUTOINCREMENT, ItemId VARCHAR, Sold VARCHAR);

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.

1, 16, 22
2, 16, 19

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 horrible data model, it is what I'm up against with no ability to change the table structure.

Is there a way to update each row with the updated Sold value when I have duplicate ItemId values, but the PK is unique?

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).

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);

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

日裸衫吸 2024-10-20 08:19:31

我想问题是，如果您只有 ItemId，您如何知道要更新哪个 ROWID？您可以尝试类似的操作：

update Items item1 set Sold = 23 where ItemId = 16 and not exists(select rowid from Items where item1.rowid < rowid)

update Items item1 set Sold = 20 where ItemId = 16 and not exists(select rowid from Items where item1.rowid > rowid)

尽管如果给定的 ItemId 的行数超过 2 行，则这不会太有用。

I guess the question would be, how do you know which ROWID you want to update if all you have is the ItemId? You could try something like:

update Items item1 set Sold = 23 where ItemId = 16 and not exists(select rowid from Items where item1.rowid < rowid)

update Items item1 set Sold = 20 where ItemId = 16 and not exists(select rowid from Items where item1.rowid > rowid)

Although this wouldn't be too useful if there are more than 2 rows for the given ItemId.

回复收藏 0 原文

梦言归人 2024-10-20 08:19:31

插入或替换语法非常适合此目的。

insert or replace into items(pk, itemid, sold)
select pk, itemid, 23 from items where itemid = 16 limit 1;

insert or replace into items(pk, itemid, sold)
select pk, itemid, 20 from items where itemid = 16 limit 1;

The insert or replace syntax is good for that.

insert or replace into items(pk, itemid, sold)
select pk, itemid, 23 from items where itemid = 16 limit 1;

insert or replace into items(pk, itemid, sold)
select pk, itemid, 20 from items where itemid = 16 limit 1;

回复收藏 0 原文

~没有更多了~