Hey guys, I’m stuck on a database issue. I need to copy some rows in my table, but with a twist. The rows have product IDs, category IDs, and positions. I want to keep the product IDs the same, but change the category IDs. Every time I try, I get a syntax error. Here’s what I’ve attempted:
INSERT INTO product_categories (product_id, '275', 0)
SELECT product_id FROM product_categories WHERE category_id = 8;
I’m working with MariaDB and using PHPMyAdmin as my interface. Any ideas on what I’m doing wrong? The position column isn’t important, it can just be 0 for all the new entries. Thanks in advance for any help!
hey sophia! have u tried using INSERT…SELECT statement? it might solve ur problem. what other approaches hve you considered? im curious about the size of ur database and how many entries ur trying to duplicate. maybe theres a more efficient way to handle this. what do u think?
I’ve encountered a similar issue before. Your SQL syntax is close, but not quite right. Here’s how you can modify it to achieve what you want:
INSERT INTO product_categories (product_id, category_id, position)
SELECT product_id, 275, 0 FROM product_categories WHERE category_id = 8;
This query will insert new rows with the same product_ids as the ones where category_id is 8, but with a new category_id of 275 and a position of 0. Make sure your table structure allows for duplicate product_ids with different category_ids. Also, double-check that you have the necessary permissions to perform INSERT operations on the table. If you’re still getting errors, sharing the exact error message may help with further troubleshooting.
hey sophia, ur query’s almost there! just a small tweak needed. try this:
INSERT INTO product_categories (product_id, category_id, position)
SELECT product_id, 275, 0 FROM product_categories WHERE category_id = 8;
this should do the trick. lemme know if u hit any snags!