Using more than one SELECT in INSERT INTO query

Question

Im trying to insert some values with multiple selects in the query, but its given me unknown column 'rate' in where clause error

 INSERT INTO oc_tax_rule (tax_class_id, tax_rate_id, based, priority) VALUES ( (SELECT tax_class_id FROM oc_tax_class WHERE title LIKE '%0%'), (SELECT tax_rate_id FROM oc_tax_rate WHERE rate ='0'), 'store', 1)

Where is the store coming from? the fields must be matched. — xdazz
– xdazz, Commented Mar 5, 2014 at 14:22
Are you trying to insert one row ans substitute some values - or to insert many rows as result of your SELECT ? — Alma Do
– Alma Do, Commented Mar 5, 2014 at 14:22
@grasshopper then - are you sure that your select's will always return one row? (my doubts are about this part: WHERE title LIKE '%0%' - what if there are titles, let's say, '103' and '5004' ?) — Alma Do
– Alma Do, Commented Mar 5, 2014 at 14:27

fthiella · Accepted Answer · 2014-03-05 14:28:13Z

You are probably looking for this:

INSERT INTO oc_tax_rule (tax_class_id, tax_rate_id, based, priority) SELECT (SELECT tax_class_id FROM oc_tax_class WHERE title LIKE '%0%' LIMIT 1), (SELECT tax_rate_id FROM oc_tax_rate WHERE rate ='0' LIMIT 1), 'store', 1

select query will return just one row, with the first and second columns that are the result of your two select query - you probably need to add a LIMIT 1 in order to make sure that only one row will be returned

So, the problem was not the syntax but that your rate is not unique or (more probable) that the title LIKE '%0%' did not return only one row.

Collectives™ on Stack Overflow

Using more than one SELECT in INSERT INTO query

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related