0

Sql

My Model table column MOD_WAIT_CHG has same datatype numeric since I am dropping & sign. My charter table has column CHAR_WAIT_CHG which is also numeric. I have to copy the data from MOD_WAIT_CHG to CHAR_WAIT_CHG I used the statement INSERT INTO CHARTER(CHAR_WAIT_CHG) SELECT MODE_WAIT_CHG FROM MODEL WHERE MOD_WAIT_CHG = 100 I m getting an error red line under CHAR_WAIT_CHG I have declared the column Please help

20th Jul 2022, 11:01 AM
Knowledge Is Power
Knowledge Is Power - avatar
20 Antworten
+ 3
I might suppose there is a field in CHARTER that matches the values in MODEL - values like 'C-90A', 'PA-23-250', 'PA31-350'. If so, then it would make sense to adjust your query like this: UPDATE CHARTER SET CHAR_WAIT_CHG = MOD_WAIT_CHG FROM MODEL WHERE CHARTER.MOD_CODE = MODEL.MOD_CODE (MOD_CODE is the name I made up for the field that holds 'C-90A', etc. in both tables)
21st Jul 2022, 4:55 AM
Brian
Brian - avatar
+ 2
Is MODE_WAIT_CHG a typographical error, or the name of a third column not mentioned?
20th Jul 2022, 2:18 PM
Brian
Brian - avatar
+ 2
Knowledge Is Power I admit that I am puzzled, too. As it is an insert, the table name in front of the field name should not be necessary. Maybe the table name needs qualification. Try <dbname>.<schema>.CHARTER [e.g., MyDB.dbo.CHARTER] SQL Server might be configured to be case sensitive. Check that every letter in the field name matches the capitalization in the defined name.
20th Jul 2022, 5:45 PM
Brian
Brian - avatar
+ 2
Now a couple more questions: 1) Is there a common key between the two tables - an ID field - that you want to match in the records where you set CHAR_WAIT_CHG = MOD_WAIT_CHG? 2) After you added the CHAR_WAIT_CHG to CHARTER, did you try refresh or disconnect+reconnect to the DB? Also try opening a new query tab.
21st Jul 2022, 3:46 AM
Brian
Brian - avatar
+ 2
If you are filling in the new field on existing records, then it would be an UPDATE.
21st Jul 2022, 4:08 AM
Brian
Brian - avatar
+ 2
Observation: The UPDATE sets CHAR_WAIT_CHG = MOD_WAIT_CHG. In the subquery, the WHERE clause takes only values of 100. So it is like setting CHAR_WAIT_CHG = 100 in every record. This would be a simpler revision: UPDATE CHARTER SET CHAR_WAIT_CHG = 100
21st Jul 2022, 4:25 AM
Brian
Brian - avatar
+ 1
Knowledge Is Power okay, so it is a typo in the SELECT field name.
20th Jul 2022, 4:23 PM
Brian
Brian - avatar
+ 1
Knowledge Is Power the syntax seems fine. Does it still complain after correcting MODE_WAIT_CHG TO MOD_WAIT_CHG? It may be that it indicates a data type mismatch with CHAR_WAIT_CHG and it failed to highlight the real problem. Also, please indicate which SQL brand you are using? If it is SQL Server you can hover the mouse over the error highlight and see more detail.
20th Jul 2022, 4:38 PM
Brian
Brian - avatar
+ 1
As a temporary distraction, let's verify that INSERT is the goal. Are you creating new records in CHARTER, or filling in the newly-added field on existing records?
21st Jul 2022, 3:34 AM
Brian
Brian - avatar
0
Brian I am inserting data in CHAR_WAIT_CHG from MOD_WAIT_CHG https://code.sololearn.com/c5ftjdfif6v4/?ref=app
20th Jul 2022, 2:25 PM
Knowledge Is Power
Knowledge Is Power - avatar
0
Brian But the syntax statement is ok to copy data from MOD_WAIT_CHG tto CHAR_WAIT_CHG? Even if the typo is ok it does not work because it doesnt recognize CHAR_WAIT_CHG :-(
20th Jul 2022, 4:28 PM
Knowledge Is Power
Knowledge Is Power - avatar
0
Brian Yes I changed to MOD_WAIT_CHG I am using Microsoft SQL Server Express 2019. It said invalid column I have added it to the table and it displays in the table which is NULL. Do i have to mention table with each column?
20th Jul 2022, 5:11 PM
Knowledge Is Power
Knowledge Is Power - avatar
0
Brian I am still struggling. keep getting an error I need to copy data from one column from a table to another table column. It is not working.
21st Jul 2022, 2:45 AM
Knowledge Is Power
Knowledge Is Power - avatar
0
Brian I am updating the table charter. I have added the new column char_wait_chg which is null now. I have to copy the data from MOD_WAIT_CHG to CHAR_WAIT_CHG
21st Jul 2022, 3:38 AM
Knowledge Is Power
Knowledge Is Power - avatar
21st Jul 2022, 3:47 AM
Knowledge Is Power
Knowledge Is Power - avatar
0
No there is no common key No ID in model tabel The charter table has default start 1,2,3.. I refreshed it but not aware of disconnecting and reconnecting
21st Jul 2022, 3:52 AM
Knowledge Is Power
Knowledge Is Power - avatar
0
The hint i was given use update and select query
21st Jul 2022, 3:58 AM
Knowledge Is Power
Knowledge Is Power - avatar
0
Update is working but it copied just one value in all the row which is 100 why only 100 There is no common column in both tables This is working UPDATE CHARTER SET CHARTER.CHAR_WAIT_CHG = MOD_WAIT_CHG FROM MODEL WHERE MOD_WAIT_CHG = ‘100’
21st Jul 2022, 4:11 AM
Knowledge Is Power
Knowledge Is Power - avatar
0
Brian thank you for your support but the syntax still does not work because both columns has different data types.
22nd Jul 2022, 1:21 AM
Knowledge Is Power
Knowledge Is Power - avatar
0
You can convert the type by using the CAST function. https://www.w3schools.com/sql/func_sqlserver_cast.asp#gsc.tab=0
22nd Jul 2022, 1:53 AM
Brian
Brian - avatar