I have already read a majority of this similarly asked question but due to the specific nature of most of the answers, none helped, or I was unable to apply the answer.
I need to convert this SELECT statement into an UPDATE statement, the SELECT statement works just fine.
SELECT sf_state_filings_cstm.recurrency_c, sf_state_filings_cstm.date_filed_c, sf_state_filings_cstm.status_c FROM sc_state_configuration_cstm INNER JOIN sc_state_configuration_sf_state_filings_1_c ON sc_state_configuration_cstm.id_c = sc_state_configuration_sf_state_filings_1_c.sc_state_c2445uration_ida INNER JOIN sf_state_filings_cstm ON sc_state_configuration_sf_state_filings_1_c.sc_state_configuration_sf_state_filings_1sf_state_filings_idb = sf_state_filings_cstm.id_c WHERE sc_state_configuration_cstm.id_c = '2d9b438e-01e1-ccb2-82e5-5721221114bb'
This is what I have so far after working through the following errors:
When I first wrote the update statement, I got:
MySQL code error 1066: Not unique table/alias: ‘sf_state_filings_cstm
Then I got this error:
1052: Column ‘recurrency_c' in field list is ambiguous
Now I have this error:
Error : Unknown column 'sc_state_configuration_cstm.id_c' in 'where clause'
None of the below links have helped so far, or I was doing something wrong
I have a feeling the answer has to do with using an alias as mentioned in one the links, using HAVING instead of WHERE, but just replacing WHERE with HAVING doesn't seem to work.
Here is my syntax right now:
UPDATE `my_database`.`sf_state_filings_cstm` LEFT OUTER JOIN sc_state_configuration_sf_state_filings_1_c AS joined_tables ON sc_state_configuration_cstm.id_c = sc_state_configuration_sf_state_filings_1_c.sc_state_c2445uration_ida LEFT OUTER JOIN sf_state_filings_cstm AS main_table ON sc_state_configuration_sf_state_filings_1_c.sc_state_configuration_sf_state_filings_1sf_state_filings_idb = sf_state_filings_cstm.id_c SET main_table.recurrency_c = 'Perpetual', main_table.expiration_date_c = '' WHERE sc_state_configuration_cstm.id_c = '2d9b438e-01e1-ccb2-82e5-5721221114bb'
Here is how the Tables have to be linked to each other:
I also realized, I need to be doing a LEFT OUTER JOIN instead of an INNER JOIN. I have updated the above syntax. The middle table stores the id's from both tables. That's how the relationship is stored. If more information is needed, let me know.
UPDATE sc_state_configuration_cstm LEFT OUTER JOIN sc_state_configuration_sf_state_filings_1_c ON sc_state_configuration_cstm.id_c = sc_state_configuration_sf_state_filings_1_c.sc_state_c2445uration_ida LEFT OUTER JOIN sf_state_filings_cstm ON sc_state_configuration_sf_state_filings_1_c.sc_state_configuration_sf_state_filings_1sf_state_filings_idb = sf_state_filings_cstm.id_c SET sf_state_filings_cstm.recurrency_c = 'Perpetual', sf_state_filings_cstm.expiration_date_c = null WHERE sc_state_configuration_cstm.id_c = '2d9b438e-01e1-ccb2-82e5-5721221114bb'
Thanks to jyncka and a lot of other posts I read, where i noticed that when converting a SELECT to an UPDATE, they simply wrote UPDATE (table from the 'FROM' statement).
I went back and noticed I had written:
The error is telling you what's happening: id_c is not a column that exists in the sc_state_configuration_cstm table. If you do a DESCRIBE on sc_state_configuration_cstm it will show you the correct field name and you can drop that in instead of id_c.
Or you might have used the wrong alias in your WHERE statement. Without knowing what your tables look like, it's difficult to say which is the case.
Edit: I believe I see the problem. Here are the tables you are explicitly using in the statement:
But you are using this table in the WHERE clause:
You need to join sc_state_configuration_cstm so that it can be used. It took me a minute to see it because sc_state_configuration_cstm and sf_state_filings_cstm look similar at first glance.
- mysql inner join too slow
- how to self join a table and get the records
- value of max_connections in AWS RDS
- How to delete specific models in Yii2?
- JOIN and COUNT wrong result
- MySQL: INSERT INTO SELECT issues
- SQL search for words in any order
- “Column 'id' in where clause is ambiguous” after inner joining?
- How to search for duplicates with cutting of a specified value
- How to join two table and show one query result in MySQL?
- of `t1.a`: mysql> SELECT * FROM t1 NATURAL LEF
- LL,101)`: mysql> SELECT * FROM t1
- e actually changed. The `mysql_info()` C API function return
- bles BKA: mysql> SET optimizer_switch='mrr=on
- t1 WHERE ...; Note The mysql client by default strips comm
- regarded as an analog of mysqlimport or the `LOAD DATA INFIL
- nloaded from https://dev.mysql.com/doc/index-other.html. You