Command: INSERT ... ON DUPLICATE

asking-fox
asking-fox used Ask the Experts™
on
I wanne amplify the command: "INSERT INTO `$table`() VALUES ($id, $row)" for the case that the row already exists.

Following append doesn't work: "ON DUPLICATE KEY UPDATE `$id` = `$row`".
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
If $id is a variable that is used in a VALUES() clause, then I don't think you'd use it in the ON DUPLICATE clause like that. I would expect to see a column name for ID below.

'$id' is some sort of variable.

>>ON DUPLICATE KEY UPDATE `$id` = `$row`".


How about:

>>ON DUPLICATE KEY UPDATE ROW = `$row`.
Split it into two SQL statements - general outline:
Update T1 set F1 = parmData where ID = parmID
Insert T1(ID, F1) Values(parmID, parmData) where not exists Select parmID from T1
Top Expert 2009

Commented:
Im also curious on which dialect this is. Is this a mixture of Perl and a specific database? Which database?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Thak you for the suggestions. I will try it later.

@ mrjoltcola:
Normal MQSK is used, called by php.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
>Following append doesn't work:
what is the exact error/problem?

did you have a primary/unique key on the table?

ON DUPLICATE KEY UPDATE `$id` = `$row`".
should probably rather:
ON DUPLICATE KEY UPDATE `row` = $row ".
 "INSERT INTO `$table`(id, `row`) VALUES ($id, $row) ON DUPLICATE KEY UPDATE `row` = $row "

Open in new window

Author

Commented:
now concrete:

# command
INSERT INTO `light`() VALUES (16, '255','255','255','255','255','255','255','255','255','255','255','255') ON DUPLICATE KEY UPDATE `16` = `'255','255','255','255','255','255','255','255','255','255','255','255'`

# error
Unknown column '16' in 'field list'
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
please review the syntax,  the ON DUPLICATE KEY needs to specify the
COLx = value, COLy = value ... etc ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial