Hi guys!
I am trying to do a code that will insert a row in table_a if a user with a user_id of 33 has a subtype of 'member' (from the login table) and if the row in table_a exists the it will just update it. Sadly it is not working
INSERT INTO table_a(uid, current, paid) VALUES (SELECT 33, 1, 0 FROM login WHERE (SELECT login.subtype FROM login WHERE login.user_id = 33) = 'member' LIMIT 1) ON DUPLICATE KEY UPDATE current=current+1
I got this error but I do not know what to do.
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 33, 1, 0 FROM table_a WHERE (SELECT login.subtype FROM login WHERE log' at line 2 [/size]
UPDATE:
I tried a different approach to this and it works but I noticed that phpmyadmin says that 2 rows were affected. What could be the reason?
INSERT INTO table_a(uid, current, paid) SELECT 33, 1, 0 FROM login WHERE EXISTS (SELECT 1 FROM login WHERE login.user_id = 33 && login.subtype = 'member') LIMIT 1 ON DUPLICATE KEY UPDATE current=current+1
UPDATE 2, SOLVED:
Upon reading I discovered that using INSERT ON DUPLICATE KEY UPDATE will return 1 row if the query inserted while it will return 2 rows if it has updated.
Edited by PuddingEatsPanda, 02 April 2016 - 04:55 PM.
