How do I add values to column with different data types in SQL?

Question

0.00/5 (No votes)

See more:

 have two tables Model and Charter.

I have to update  new attributes to charter table with is CHAR_WAIT_CHG that has numeric data type with ALTER and add it. The Model table has MOD_WAIT_CHG which has numeric data type and has values 100,50, and 75. I do not see any matching columns in both tables.

The values in MOD_WAIT_CHG should be copied into the column in Charter table. But I am having very hard time implementing it. Please help 

My sql statement is 

UPDATE CHARTER 

 SET CHAR_WAIT_CHG = MOD_WAIT_CHG FROM MODEL

 WHERE CHARTER.MOD_CODE = MODEL.MOD_CODE

The tables are

CREATE TABLE CHARTER (
CHAR_TRIP int,
CHAR_DATE datetime,
AC_NUMBER varchar(5),
CHAR_DESTINATION varchar(3),
CHAR_DISTANCE float(8),
CHAR_HOURS_FLOWN float(8),
CHAR_HOURS_WAIT float(8),
CHAR_FUEL_GALLONS float(8),
CHAR_OIL_QTS integer,
CUS_CODE int
);
I added new column CHAR_WAIT_CHG datatype numeric and I have to update the table with value copied from Model table MOD_WAIT_CHG.
MODEL TABLE IS
CREATE TABLE MODEL (
MOD_CODE varchar(10),
MOD_MANUFACTURER varchar(15),
MOD_NAME varchar(20),
MOD_SEATS float(8),
MOD_CHG_MILE float(8)
);
The new column I added in MODEL TABLE IS MOD_WAIT_CHG that has 100
So 100 should be copied into CHAR_WAIT_CHG. Since the datatypes are different I cannot implement it. There is no common column in both tables. 
Please help!

What I have tried:

<pre>UPDATE CHARTER 

 SET CHAR_WAIT_CHG = MOD_WAIT_CHG FROM MODEL

 WHERE CHARTER.MOD_CODE = MODEL.MOD_CODE

Posted 22-Jul-22 18:24pm

CSDream12

Updated 25-Jul-22 6:56am

Add a Solution

Comments

CHill60 25-Jul-22 12:45pm

"There is no common column in both tables" - yet in your SQL you have

WHERE CHARTER.MOD_CODE = MODEL.MOD_CODE

Is it in the charter table or not?
"Since the datatypes are different" - you haven't told us what data type you have used for MOD_WAIT_CHG just that it contains 100, which is Numeric, so not different.
Edit your question and show us the table schema after you have added the extra columns (or at least share the ALTER table script)
Then give us some sample data for each table, and what you want the results to be

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-07-25T06:56:00

This is probably the wrong thing to do but your question is not clear at all.

To change the type of data in SQL you can use CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Docs[^]
Example:
Sample data:

SQL

declare @demo1 table (linkingColumn int, datum varchar(20));
insert into @demo1 (linkingColumn, datum) values
(1, '50')	-- never do this in the real world!
,(2, '150');

declare @demo2 table (linkingColumn int, datum int);
insert into @demo2 (linkingColumn, datum) values
(1,100)
,(2,200);

Using CAST

SQL

update t1
set datum = cast(t2.datum as varchar(20))
from @demo1 t1
join @demo2 t2 on t1.linkingColumn = t2.linkingColumn;

select * from @demo1;

update t2
set datum = cast(t1.datum as int)
from @demo1 t1
join @demo2 t2 on t1.linkingColumn = t2.linkingColumn;

select * from @demo2;

Using CONVERT

SQL

update t1
set datum = convert(varchar(20), t2.datum )
from @demo1 t1
join @demo2 t2 on t1.linkingColumn = t2.linkingColumn;

update t2
set datum = convert(int, t1.datum)
from @demo1 t1
join @demo2 t2 on t1.linkingColumn = t2.linkingColumn;

NOTE my comment "-- never do this in the real world". You should always store data in the correct column type, numerics in numerical columns, date in date or datetime, etc then you will not suffer from this problem

Also NOTE if you are trying to update a table from another table, then you must have some means of JOINing those tables, otherwise there is no way to control which row gets which value