Need help for a SQL request

ggppdk
Offline
Administrator

6 years 8 months ago - 6 years 8 months ago #76752 by ggppdk

Replied by ggppdk on topic Need help for a SQL request

Hello

the query that i gave you is for replacing existing values
because that is what i understood from your original request

The insert will use a SELECT for values

something like: (i have not tested it)

Code:

INSERT INTO `PPP_flexicontent_fields_item_relations`
   (field_id, item_id, valueorder, suborder, value, value_integer, value_decimal, value_datetime)

SELECT c.itemid, 602, 1, 1,  'Générique', 0, 0, NULL
FROM  `PPP__flexicontent_cats_item_relations` AS c
 
INNER JOIN `PPP_flexicontent_items_ext` AS e
ON c.itemid = e.item_id AND e.type_id = 6
 
WHERE c.catid = 56

-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star review. Thanks!

Last edit: 6 years 8 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

bendeb
Topic Author
Offline
Platinum Member

6 years 8 months ago - 6 years 8 months ago #76758 by bendeb

Replied by bendeb on topic Need help for a SQL request

Many thanks ggppdk, it works now

You just invert some fields (field_id and item_id), so correct request is as follow :

Code:

INSERT INTO `PPP_flexicontent_fields_item_relations`
   (item_id, field_id, valueorder, suborder, value, value_integer, value_decimal, value_datetime)
 
SELECT c.itemid, 602, 1, 1,  'Générique', 0, 0, NULL
FROM  `PPP_flexicontent_cats_item_relations` AS c
 
INNER JOIN `PPP_flexicontent_items_ext` AS e
ON c.itemid = e.item_id AND e.type_id = 6
 
WHERE c.catid = 56

And because I have no use of type, I can just use these request :

Code:

INSERT INTO `PPP_flexicontent_fields_item_relations`
   (item_id, field_id, valueorder, suborder, value, value_integer, value_decimal, value_datetime)
 
SELECT c.itemid, 602, 1, 1,  'Générique', 0, 0, NULL
FROM  `PPP_flexicontent_cats_item_relations` AS c
 
WHERE c.catid = 56

Now, I have just to replace category_id and value and I can edit all my items at once.

Thank you very much.

Flexicontent 4.1.0b1
Joomla 3.9.18

Last edit: 6 years 8 months ago by bendeb.

Please Log in or Create an account to join the conversation.