I would like to create a new column based on the amount of an existing column. I am getting an error with my code below and I understand it is because <=, >= type operands can only return one record. I believe I should be using an IN or ANY, ALL possibly operand but cannot seem to implement it properly.
ALTER TABLE SALES_TOTAL ADD REFUNDS INTEGER(20);
UPDATE SALES_TOTAL SET REFUND = SELECT [SALE_TOTAL] from [SALES_TOTAL] WHERE[SALES_TOTAL] < 10
That's what you probably want:
alter table SALES_TOTAL add REFUNDS int unsigned not null default 0; update SALES_TOTAL set REFUNDS = SALE_TOTAL where SALE_TOTAL < 10;