0

I've seen this with almost the same problems as me MySQL INSERT IF (custom if statements).

but I did not successfully execute this query:

INSERT into cart_items (product_id,product_variant_id,product_attribute_id,cart_id,quantity)
select (7,7,12,11,4)
where (select pa.stock from product_attribute as pa where pa.id = 12) > 4

in this case this is (7,7,12,11,4) value of this (product_id,product_variant_id,product_attribute_id,cart_id,quantity)

product_id: 7

product_variant_id :7

product_attribute_id : 12

cart_id : 11

quantity : 4

in my headiSQL i got error like this :

SQL (Error(1241) : Operand should contain 1 column(s)

Table cart_items

ID : Primary key autoincrement
product_variant_id : Foreign key
product_attribute_id : Foreign key
product_id : Foreign key
cart_id : Foreign key
quantity :int

Table product_attribute

ID : Primary key autoincrement
product_id : Foreign key
product_variant_id :Foreign key
size : varchar
stock : int

I want to make query if cart_items quantity less than product_attribute stock than insert

Faris Dewantoro
  • 1,597
  • 4
  • 17
  • 31

2 Answers2

0
INSERT into cart_items (product_id,product_variant_id,product_attribute_id,cart_id,quantity)
SELECT product_id,product_variant_id,product_attribute_id,cart_id,quantity FROM product_attribute
WHERE id = ? && (SELECT stock from product_attribute WHERE id = ?) > (SELECT quantity FROM cart_items WHERE product_id = ?)

This makes sure that the stock amount is more than the cart quantity. Make sure the product ids match (?)

Also, I am not sure if you meant to do an INSERT from SELECT, but because you provided raw integer values in your example, I have changed it to be INSERT (columns) VALUES (values) because that's the correct format based on your example.

Nerdi.org
  • 895
  • 6
  • 13
  • 2
    A `VALUES` clause cannot use `WHERE`. – Tim Biegeleisen Jan 14 '19 at 01:36
  • Woops, total brainfart - you're right, I was thinking it was an UPDATE statement by the time I swapped SELECT to VALUES. I wonder if a 'case' query would be good for this even – Nerdi.org Jan 14 '19 at 01:44
  • A SELECT can't take a `(resultlist)`, it needs a `FROM` statement, probably just accept Tim's answer – danblack Jan 14 '19 at 01:44
  • Yeah, but OP's issue is with the double select to compare the two values... I'm guessing he knows how to construct an INSERT / SELECT query - he only hardcoded the values as an example – Nerdi.org Jan 14 '19 at 01:49
  • this is ` (7,7,12,11,4)` values not comming from other table so i think i dont need to do like this `SELECT product_id,product_variant_id,product_attribute_id,cart_id,quantity FROM product_attribute` – Faris Dewantoro Jan 14 '19 at 01:54
  • I want to check before insert, first check stock in table product_attribute if the values for cart_items is mean quantity is less than stock and then if true insert it – Faris Dewantoro Jan 14 '19 at 01:56
0

Try selecting from the dummy DUAL table:

INSERT into cart_items (product_id, product_variant_id, product_attribute_id,
    cart_id, quantity)
SELECT 7, 7, 12, 11, 4
FROM dual
WHERE (SELECT pa.stock FROM product_attribute pa WHERE pa.id = 12) > 4;

I don't know if the above logic is what you actually intend to run, but it at least should run without syntax errors.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360