Using SET in INSERT ON DUPLICATE KEY UPDATE

anjanesh · #1 (**permalink**) 03-01-07, 06:17

Hi

How do I get this to work ?

Code:

INSERT INTO `tbl`
SET `PK`=3, `FK`=1, `Data1` = 'data1', `Data2` = 'data2'
ON DUPLICATE KEY UPDATE SET `FK`=1, `Data1` = 'data1', `Data2` = 'data2' 
WHERE PK`=3

I need to use SET instead of VALUES() for INSERT because `FK`=1, `Data1` = 'data1', `Data2` = 'data2' is actually one long assignment line and it would be used from a PHP variable $set. So it'll turn out like this :

Code:

INSERT INTO `tbl`
SET `PK`=3, $set
ON DUPLICATE KEY UPDATE SET $set WHERE PK`=3

Thanks

healdem · #2 (**permalink**) 03-01-07, 06:23

so whats stopping you using conventional standards compliant SQL?

Why do you need to use SET?

whats wrong with you building you insert statement within your PHP string

anjanesh · #3 (**permalink**) 03-01-07, 06:36

If I got to use INSERT INTO tble ('f1', 'f2', 'f3') VALUES ('a', 'b', 'c') then I got to make a $set2 to create this assignments.
I was hoping to use the same $set for INSERT and UPDATE ON DUPLICATE KEY.

r937 · #4 (**permalink**) 03-01-07, 06:46

remove the word SET after the word UPDATE, and remove the WHERE clause after the word UPDATE

Internet Services

Web Development

Digital Marketing

Consumer Tech