dave08
11/06/2023, 12:16 PMToshihiro Nakamura
11/06/2023, 12:27 PMdave08
11/06/2023, 12:29 PMia
that I need to update, ia
contains a foreign key to a
and I need a where on a.name
for which row in ia
to update...UPDATE items,
(SELECT id, retail / wholesale AS markup, quantity FROM items)
AS discounted
SET items.retail = items.retail * 0.9
WHERE discounted.markup >= 1.3
AND discounted.quantity < 100
AND items.id = discounted.id;
UPDATE ia,
(SELECT id, name FROM a)
AS a1
SET ia.status = 3
WHERE ia.user = 20
AND a1.name = ?;
mysql> UPDATE items
> SET retail = retail * 0.9
> WHERE id IN
> (SELECT id FROM items
> WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause
Toshihiro Nakamura
11/06/2023, 12:46 PMdave08
11/06/2023, 1:16 PMupdate ia
set ia.status = 2
where
ia.pId in (select p.id from p where p.name = 'someName' and ia.user = 180411;
So I guess it would be:
QueryDsl.update(ia).set {
ia.status eq Status.SomeStatus
}.where {
ia.aId inList QueryDsl.from(a).where { a.name eq name }.select(a.id)
}
Toshihiro Nakamura
11/07/2023, 5:28 AM