0

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!

gloomy.penguin
  • 5,833
  • 6
  • 33
  • 59
  • "but it told me something about no DDL or DML statements" --- then fix it somehow. Or share the **exact** error message. – zerkms Aug 19 '13 at 22:48
  • @zerkms - ItemID isn't updated with the same value...?? and that was pretty much the exact error I got and it had nothing to do with the validity of the statement (which was just me saying why I really can't test myself right now)... apparently just an issue with what panel I placed the statement into to test run. – gloomy.penguin Aug 19 '13 at 22:57
  • SQLFiddle Error: `DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel.` (which is fixed by moving the insert/on-dup-key statement to the schema panel and doing `select * from table` in the sql panel like explosion pills said) – gloomy.penguin Aug 19 '13 at 23:08

1 Answers1

0

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.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • Actually, unfortunately, I think the Value might be the key in this situation... or they might both be... I can't tell by what I've been given, only past experience with the person who requested stuff. I don't have access to the DB until tomorrow morning so I just stuck both of them in there. I just got a set of queries tonight and they're all over the place so I'm attempting to clean it up w/o being able to look. – gloomy.penguin Aug 19 '13 at 23:12