Update two rows in MS Access by SQL Query

Question

How can i update two rows with one query in ms access...

I tested manny codes but noone worked for me...

UPDATE tblSecondProductItems 
SET secondProductItemLot = CASE
when secondProductItemCode = '616161911659' then 20
when secondProductItemCode = '611611965166' then 21
end
where secondProductItemCode in('616161911659','611611965166')

Can you elaborate on the problem this statement is causing? Does it error out? produce the wrong result? — Mureinik
– Mureinik, Commented Mar 2, 2017 at 13:13
It commes error: syntax error (missing operator) in query expression 'CASE when secondProductItemCode = '616161911659' then 20 when secondProductItemCode = '611611965166' then 21 end — Pecurka
– Pecurka, Commented Mar 2, 2017 at 13:13

Gordon Linoff · Accepted Answer · 2017-03-02 13:14:51Z

1

MS Access doesn't support CASE. You can use SWITCH():

UPDATE tblSecondProductItems 
    SET secondProductItemLot = SWITCH(secondProductItemCode = '616161911659', 20,
                                      secondProductItemCode = '611611965166', 21
                                     )
    WHERE secondProductItemCode IN ('616161911659','611611965166')

answered Mar 2, 2017 at 13:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christopher Gallé · Accepted Answer · 2017-03-02 13:20:39Z

0

You can try something like

UPDATE tblSecondProductItems 
CASE
    when secondProductItemCode == '616161911659' then secondProductItemCode = 20
    when secondProductItemCode == '611611965166' then secondProductItemCode = 21
END
WHERE secondProductItemCode in('616161911659','611611965166')

answered Mar 2, 2017 at 13:20

Christopher Gallé

611 silver badge5 bronze badges

Collectives™ on Stack Overflow

Update two rows in MS Access by SQL Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related