Click here to Skip to main content
15,502,868 members
Please Sign up or sign in to vote.
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
Updated 25-Jul-22 7:56am
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

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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900