UPDATE - JOIN on WHERE clause

[Adamhumbug]

Hi All,

I have a system where you create quotes.

i have a table full of items and a table of quote items.

I want to update all quote items (discount_percentage and discounted_price) with a value where the item in the items table has discountable set to 1.

quote_items 	CREATE TABLE `quote_items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `quote_id` int(11) NOT NULL,
 `item_id` int(11) NOT NULL,
 `quantity` int(11) NOT NULL,
 `start_date` varchar(30) DEFAULT NULL,
 `end_date` varchar(30) DEFAULT NULL,
 `notes` varchar(250) NOT NULL,
 `amount_charged_each` float(10,2) NOT NULL,
 `original_price_each` float(10,2) NOT NULL,
 `chargable_units` float(10,2) NOT NULL,
 `discounted_price` float(10,2) DEFAULT NULL,
 `discount_percentage` decimal(5,2) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

items 	CREATE TABLE `items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `section_id` int(11) NOT NULL,
 `name` varchar(200) NOT NULL,
 `GBP` float NOT NULL DEFAULT 0,
 `USD` float NOT NULL DEFAULT 0,
 `CAD` float NOT NULL DEFAULT 0,
 `EUR` float NOT NULL DEFAULT 0,
 `charge_by_id` tinyint(20) NOT NULL,
 `discountable` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_c

I am not really sure where to start with this one.

 

I tried this but nothing is being updated - no errors reported though, which makes me think i am close.

 

UPDATE quote_items qi 
inner join items i on qi.item_id = i.id
set qi.discounted_price = qi.amount_charged_each * .5, qi.discount_percentage = .5 where qi.id = 89 and i.discountable = 1

 

Edited September 18, 2023 by Adamhumbug

[Adamhumbug]

I knew i was close:

UPDATE quote_items qi 
inner join items i on qi.item_id = i.id
set qi.discounted_price = qi.amount_charged_each * .5, qi.discount_percentage = .5 where qi.quote_id = 89 and i.discountable = 1