Page 1 of 1

Help: Query INSERT INTO...SELECT...ON DUPLICATE KEY UPDATE

Posted: Fri Nov 26, 2010 8:22 am
by Bbob
Hi

Heres my table structure

TABLE
customerinventory
COLUMN
itemid, itemname,itemqty ,itemtype,total, status, customerid, update

TABLE
withdraw
COLUM
wid, itemid, itemname, itemtype, qty, wtotal, customerid, delivdate, payoption, requestdate, responsedate, status


Im using this query and getting an error of [text]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 ''itemid', '', itemqty, '', '', '', '', '', '') SELECT qty FROM withd' at line 1[/text]

Code: Select all

mysql_query("INSERT INTO customerinventory c ('itemid', '', itemqty, '', '', '', '', '', '')
				 SELECT qty
				 FROM withdraw w
				 WHERE wid = '$wid'
				 ON DUPLICATE KEY UPDATE itemqty = itemqty - qty") or die(mysql_error());
What Im trying to do is just update the itemqty from customer inventory

Re: Help: Query INSERT INTO...SELECT...ON DUPLICATE KEY UPDA

Posted: Fri Nov 26, 2010 9:02 am
by Darhazer

Code: Select all

mysql_query("INSERT INTO customerinventory c ('itemid', '', itemqty, '', '', '', '', '', '')
you have to specify the colum names.
If you want to insert empty values, you have to select them, not put empty strings in the insert
for example:

Code: Select all

mysql_query("INSERT INTO customerinventory c (`itemid`, `itemname`, `itemqty`, `itemtype`, `total`, `status`, `customerid`, `update`)
                                 SELECT itemid, '', qty, '', '', '', '', ''
                                 FROM withdraw w
                                 WHERE wid = '$wid'
                                 ON DUPLICATE KEY UPDATE itemqty = itemqty - qty") or die(mysql_error());