user3125923 user3125923 - 7 months ago 12
SQL Question

SQL create new column based on a less than operand of another column

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


Could anyone provide an example of how to do so?

Thanks in advance.

Answer

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