Insert into....select query returning blank

Question

I'm trying to execute the following command through straight sql execution and it runs fine without any errors but it's not actually working.

INSERT INTO log_inventory(CREATED_TIME, PRODUCT_ID, LOG_INV_CHANGE, LOG_INV_ACTION, LOG_INV_NUM_HISTORY, USER_ID)
SELECT now(), p.PRODUCT_ID, p.PRODUCT_INVENTORY, 'Manual Inventory Override', 0, 1
FROM product AS p WHERE p.PRODUCT_INVENTORY != 0;

The select query works because if I run that separately, it returns all the rows I need, but the insert portion doesn't work. Am I missing something?

What client are you testing this in? The syntax is all correct. — Michael Berkowski
– Michael Berkowski, Commented Jan 22, 2015 at 21:41
InnoDB Mysql, running the sql query inside phpmyadmin and it shows no errors. — Andres S
– Andres S, Commented Jan 22, 2015 at 22:19
This is one of those things where you just need to quadruple check that you're working with the right db connection and the table has rows in it. Does the target table already have some rows, or is it empty? — Michael Berkowski
– Michael Berkowski, Commented Jan 23, 2015 at 0:18
Like I said, the select works and returns rows, the target table is empty though. I am definitely working in the right database, especially because I'm in phpmyadmin and it's hard not to be. — Andres S
– Andres S, Commented Jan 23, 2015 at 3:43
Wow, idk what I was doing wrong, I think it was just me being tired, it does work. — Andres S
– Andres S, Commented Jan 23, 2015 at 17:11

iubema · Accepted Answer · 2015-01-22 22:41:01Z

1

Supposing the query has results, try this:

INSERT INTO log_inventory(CREATED_TIME, PRODUCT_ID, LOG_INV_CHANGE, LOG_INV_ACTION, LOG_INV_NUM_HISTORY, USER_ID)
(
    SELECT now(), p.PRODUCT_ID, p.PRODUCT_INVENTORY, 'Manual Inventory Override', 0, 1
    FROM product AS p 
    WHERE p.PRODUCT_INVENTORY != 0
);

Hope that helps

answered Jan 22, 2015 at 22:41

iubema

3492 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andres S Over a year ago

It was working all along, idk what I was doing wrong. Since your comment made me try again, I'll mark it as answered, sorry!

Collectives™ on Stack Overflow

Insert into....select query returning blank

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related