Sql

Question

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

Answer 1

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)

Answer 2

Is MODE_WAIT_CHG a typographical error, or the name of a third column not mentioned?

Answer 3

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.

Answer 4

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.

Answer 5

If you are filling in the new field on existing records, then it would be an UPDATE.

Answer 6

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

Answer 7

Knowledge Is Power okay, so it is a typo in the SELECT field name.

Answer 8

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.

Answer 9

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?

Answer 10

Brian I am inserting data in CHAR_WAIT_CHG from MOD_WAIT_CHG
https://code.sololearn.com/c5ftjdfif6v4/?ref=app

Answer 11

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
:-(

Answer 12

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?

Answer 13

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.

Answer 14

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

Answer 15

https://code.sololearn.com/c5ftjdfif6v4/?ref=app

Answer 16

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

Answer 17

The hint i was given use update and select query

Answer 18

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’

Answer 19

Brian thank you for your support but the syntax still does not work because both columns has different data types.

Answer 20

You can convert the type by using the CAST function.
https://www.w3schools.com/sql/func_sqlserver_cast.asp#gsc.tab=0