No replies to this topic

[PuddingEatsPanda]

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.