I have no place to test this right now and I was hoping someone knows so I don't have to wait until tomorrow to find out....
insert into item_properties (ItemID, PropID, Value, UpdateOn) values
(538, 25, 'some description stuff goes here', unix_timestamp()),
(541, 25, 'some description stuff goes here', unix_timestamp()),
(1276, 25, 'some description stuff goes here', unix_timestamp()),
(1319, 25, 'some description stuff goes here', unix_timestamp())
on duplicate key update
ItemID = values(ItemID),
PropID = values(PropID),
Value = values(Value),
UpdateOn = values(UpdateOn)
Can that be re-written to be:
insert into item_properties (ItemID, Value) values
(538, 'some description stuff goes here'),
(541, 'some description stuff goes here'),
(1276, 'some description stuff goes here'),
(1319, 'some description stuff goes here')
on duplicate key update
ItemID = values(ItemID),
Value = values(Value),
PropID = 25,
UpdateOn = unix_timestamp()
Yes?
Or no, because the PropID
and UpdateOn
can't be accessed by the on dup
part without being in the values list...?
I tried to SQLFiddle but it told me something about no DDL or DML statements, only selects.
So I tested the filddle...
insert into item_properties (ItemID, Value) values
(538, 'some description stuff goes here'),
(538, 'some other description stuff goes here'),
(1276, 'some description stuff goes here'),
(1319, 'some description stuff goes here')
on duplicate key update
ItemID = values(ItemID),
PropID = 26,
Value = values(Value),
UpdateOn = unix_timestamp()
turns into:
ITEMID PROPID VALUE UPDATEON
538 26 some other description stuff goes here 1376952345
1276 (null) some description stuff goes here (null)
1319 (null) some description stuff goes here (null)
Which isn't the intended output...
So... I guess the two things really don't do the same thing and I need to not re-write that code in the way I initially suggested. It is valid syntax, but not correct results.
just to clarify (but I'm sure you could tell by the initial on duplicate key
statement), this is the output I should end up with...
ITEMID PROPID VALUE UPDATEON
538 26 some other description stuff goes here 1376952345
1276 26 some description stuff goes here 1376952345
1319 26 some description stuff goes here 1376952345
Thanks for the help!
It is valid SQL. That is to say that the above two INSERT ... ON DUPLICATE KEY UPDATE
statements will have identical effects.
This can be shown via SQLFiddle, it's just that the inserts have to be part of the DDL.
http://sqlfiddle.com/#!2/56579/1/0
The ItemID = VALUES(ItemID)
is also pointless if that is the duplicate key.