Need to Add Values to Certain Items-CodePudding

I have a table that I need to add the same values to a whole bunch of items

(in a nut shell if the item doesn't have a UNIT of "CTN" I want to add the same values i have listed to them all)

I thought the following would work but it doesn't :(

Any idea what i am doing wrong ?

INSERT INTO ICUNIT 
(UNIT,AUDTDATE,AUDTTIME,AUDTUSER,AUDTORG,CONVERSION)
VALUES ('CTN','20220509','22513927','ADMIN','AU','1')
WHERE ITEMNO In  '0','etc','etc','etc'

CodePudding user response：

If I understand correctly you might want to use INSERT INTO ... SELECT from original table with your condition.

INSERT INTO ICUNIT (UNIT,AUDTDATE,AUDTTIME,AUDTUSER,AUDTORG,CONVERSION)
SELECT 'CTN','20220509','22513927','ADMIN','AU','1'
FROM ICUNIT
WHERE ITEMNO In  ('0','etc','etc','etc')

CodePudding user response：

The query you needs starts by selecting the filtered items. So it seems something like below is your starting point

select <?> from dbo.ICUNIT as icu where icu.UNIT <> 'CTN' order by ...;

Notice the use of schema name, terminators, and table aliases - all best practices. I will guess that a given "item" can have multiple rows in this table so long as ICUNIT is unique within ITEMNO. Correct? If so, the above query won't work. So let's try slightly more complicated filtering.

select distinct icu.ITEMNO 
from dbo.ICUNIT as icu
where not exists (select * from dbo.ICUNIT as ctns 
     where ctns.ITEMNO = icu.ITEMNO  -- correlating the subquery
       and ctns.UNIT = 'CTN') 
order by ...;

There are other ways to do that above but that is one common way. That query will produce a resultset of all ITEMNO values in your table that do not already have a row where UNIT is "CTN". If you need to filter that for specific ITEMNO values you simply adjust the WHERE clause. If that works correctly, you can use that with your insert statement to then insert the desired rows.

insert into dbo.ICUNIT (...) 
select distinct icu.ITEMNO, 'CTN', '20220509', '22513927', 'ADMIN', 'AU', '1'
from ...
;