3606 two values were given for one field error

Database
Enthusiast

3606 two values were given for one field error

Hi Experts,

I am trying to run below query but its giving error . Is there any other way write below query in one sql.

update ABC

set elg_flg= case when cnt_grt_eql_50k>=90 then 'Y' end, inc_amt=case when cnt_grt_eql_50k>=90 then 500 end,

elg_flg= case when cnt_grt_eql_40k>=90 then 'Y' end, inc_amt=case when cnt_grt_eql_40k>=90 then 400 end,

elg_flg= case when cnt_grt_eql_30k>=90 then 'Y' end, inc_amt=case when cnt_grt_eql_30k>=90 then 300 end,

elg_flg= case when cnt_grt_eql_20k>=90 then 'Y' end, inc_amt=case when cnt_grt_eql_20k>=90 then 200 end,

elg_flg= case when cnt_grt_eql_10k>=90 then 'Y' end, inc_amt=case when cnt_grt_eql_10k>=90 then 100 end,

elg_flg= case when cnt_grt_eql_5k>=90 then 'Y' end, inc_amt=case when cnt_grt_eql_5k>=90 then 50 end

where elg_flg is null;

Error :- 3606 two values were given for one field error

Thanks,

Nilesh

1 REPLY
Enthusiast

Re: 3606 two values were given for one field error

Nilesh,
This should work for you. I added an ELSE, you may not want it.
UPDATE ABC
SET elg_flg=
CASE
WHEN cnt_grt_eql_50k>=90 THEN 'Y'
WHEN cnt_grt_eql_40k>=90 THEN 'Y'
WHEN cnt_grt_eql_30k>=90 THEN 'Y'
WHEN cnt_grt_eql_20k>=90 THEN 'Y'
WHEN cnt_grt_eql_10k>=90 THEN 'Y'
WHEN cnt_grt_eql_5k>=90 THEN 'Y'
ELSE 'P'
END,
inc_amt=
CASE
WHEN cnt_grt_eql_50k>=90 THEN 500
WHEN cnt_grt_eql_40k>=90 THEN 400
WHEN cnt_grt_eql_30k>=90 THEN 300
WHEN cnt_grt_eql_20k>=90 THEN 200
WHEN cnt_grt_eql_10k>=90 THEN 100
WHEN cnt_grt_eql_5k>=90 THEN 50
ELSE 999
END
WHERE elg_flg IS NULL;

Rglass