Query to manipulate the jot_fields table

Here’s one for all you SQL experts.

I’m forcing Jot to perform to my specifications, and one tweak involves including some Javascript to allow re-ordering the items (like the drag-and-drop re-ordering of TVs, or plugin execution). The thing is, quite a number of "items" have already been entered, and adding a new custom field won’t have any effect on these existing items. So what I need to do is to go into the jot_fields table and give each item a "position" with a value of 0 (this will get fixed by the AJAX processing code the first time an item is moved).

Now, I can patiently go through the main jot_content table and for each distinct item in that table, add a row in the jot_fields table. But I know there is a (probably improbably convoluted) query that can do it all in one query! Can somebody help with this?

MySQL: 5.1.45-log

Well, I did it with 2 queries, the first


This set up the (empty) rows for each item, then a subsequent query to update the rows where the label field was empty was easy enough.

I’m still quite sure it could have all been done in one query, though.

You can use "constants" in queries:

That looks really weird... even though the "label" and "content" fields don’t exist in the jot_content field?

please trust me or test this query on a backup. Or test the select-part only:

I tried the insert query successfully on my xampp-installation.

even though the "label" and "content" fields don’t exist in the jot_content field?

yes, even though. that’s the trick, you needed from "SQL experts" (i’m running several access-applications, MS SQL-server-installations and mysql-servers).


my english education is more than 20 years ago - so i can’t describe it better.

Sorry, I didn’t mean to imply that I was questioning this, it just looks really odd. I have no doubt that it works perfectly, I’m just having trouble getting my head wrapped around it, and wanted to make sure there was no misunderstanding in what I was asking.

Never mind - i didn’t take it as an offense (does my second answer sounds angry? i didn’t want that.)

The insert query in my first answer will insert the "id"s of "modx_jot_content" into "modx_jot_fields" and the fields "label" and "content" of "modx_jot_fields" will get the constant values (no backticks there! but ’) "position" and "0". You can see the result with the select statement - it will deliver:

id;label;content;
1;position;0
2;position;0
3;position;0
...

i hope, that was it, what you wanted to insert in "modx_jot_fields"

I used your most excellent query on the production site when I put this into production and it worked great. Just what I was looking for! Thank you