MySQL IF..ELSE with subqueries

Question

I have a query

SELECT IF((select COUNT(*) from produkty where name='ASROCK 760GM-GS3' and id_nokaut='7507601876693181035' and found ='0')=0, (insert into produkty (
         id_nokaut,
         name,
         shop_count,
         offer_count,
         price_min,
         price_max,
         price_avg,
         url,
         opis,
         socket,
         typ_pamieci,
         co,
         image_mini,
         image_medium,
         image_large,
         rate,
         thumbnail,
         image,
         found)
         values(
         '7507601876693181035',
         'ASROCK 760GM-GS3',
         '4',
         '4',
         '171,31',
         '195,00',
         '179,88',
         'http://www.nokaut.pl/plyty-glowne/asrock-760gm-gs3.html',
         'ASRock 760GM-GS3... ',
         'false',
         'false',
         'plyta',
         'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc90x90/asrock-760gm-gs3.jpg',
         'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc130x130/asrock-760gm-gs3.jpg',
         'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc500x500/asrock-760gm-gs3.jpg',
         '0.00',
         'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc90x90/asrock-760gm-gs3.jpg',
         'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc130x130/asrock-760gm-gs3.jpg',
         '1')), (update produkty set  id_nokaut = '7507601876693181035', shop_count = '4', offer_count = '4', price_min = '171,31', price_max = '195,00', price_avg = '179,88', url = 'http://www.nokaut.pl/plyty-glowne/asrock-760gm-gs3.html', opis = 'ASRock 760GM-GS3... ',  socket = 'false', typ_pamieci = 'false', co = 'plyta', image_mini = 'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc90x90/asrock-760gm-gs3.jpg', image_medium = 'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc130x130/asrock-760gm-gs3.jpg', image_large = 'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc500x500/asrock-760gm-gs3.jpg', rate = '0.00', thumbnail = 'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc90x90/asrock-760gm-gs3.jpg', image = 'http://nokautimg1.pl/p-da-99-da99e9e7b3eaebef049d1234fc5c15dc130x130/asrock-760gm-gs3.jpg', found = '1' where  name = 'ASROCK 760GM-GS3'));

I'm trying to execute it in MySQL but it gives mi error:

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the ma nual that corresponds to your MySQL server version for the right syntax to use n ear 'into produkty ( id_nokaut, name, shop_cou' at line 1

I can't figure out what is wrong, can anybody help?

Thanks

It looks like you're going a very difficult way about doing REPLACE or INSERT ... ON DUPLICATE KEY UPDATE. However, if you must use IF, then you want MySQL's IF statement - which does not form part of a SELECT command - rather than the IF function that you're currently using (I know, it's confusing!). — eggyal
– eggyal, Commented Apr 26, 2012 at 17:00

Quassnoi · Accepted Answer · 2012-04-26 17:02:58Z

3

INSERT
INTO    produkty (id_nokaut, name, ...)
VALUES  ('7507601876693181035', 'ASROCK 760GM-GS3', ...)
ON DUPLICATE KEY
UPDATE
        shop_count = VALUES(shop_count),
        offer_count = VALUES(offer_count),
        ...

answered Apr 26, 2012 at 17:02

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

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

5 Comments

piotrm Over a year ago

OP may need a way to update multiple rows and insert when none is found, this approach is valid only for a single row.

Quassnoi Over a year ago

@piotrm: id usually implies a PRIMARY KEY.

Quassnoi Over a year ago

@piotrm: it's a target column in the UPDATE. The query would fail if multiple records satisfied the filter.

piotrm Over a year ago

The way count(*) and update is used suggests its a foreign key to some other table.

Quassnoi Over a year ago

@piotrm: it's possible but hardly feasible. Don't think a table can be that denormalized.

Collectives™ on Stack Overflow

MySQL IF..ELSE with subqueries

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related