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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Bbob
Forum Commoner
Posts: 57
Joined: Sat Aug 07, 2010 4:46 am

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

Post 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
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

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

Post 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());
Post Reply