473,226 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

Update One Based on ANother Table

Here is my situation;

I have two tables in a MS-SQL DB. One table with dollar amounts and service
codes. I have a second table that I want to move some information into from
the first table. The catch is I want to move one field as is from the first
table to the second, but the rest of the fields in the second table are
calculations based on fields in the first table.

The first table is called XFILE. It has fields SVCCODE, PRICE, DWAGES,
DMATLS, etc. The second table has the same field names and I want to move
the SVCCODE from XFILE to Cost_Percent with no changes. For DWAGES in the
Cost_Percent table I want to do the following calculation;

[ XFILE.DWAGE] divided by [XFILE.PRICE] and put the results in Cost_Percent
table DWAGES field

So basically I am putting a percent in the Cost_Percent table. I can move
the data from one table to another ok, but I can not figure out how to write
the query in the Query Analyzer to do this.

I am ruining SQL2000 Standard on a Win2K3 server. I am using Query Analyzer
and SQL Enterprise Manager from an XP-Pro WS.

I have looked in the 'Books On-Line' for the answer but I sort of new to SQL
and can't find the answer that I am sure is staring me in the face.

Thanks in advance for any help.

Mike Charney
m charney at dunlap hospital dot org
Oct 13 '05 #1
5 5902
INSERT INTO Cost_Percent (svccode, price, dwages, dmatls)
SELECT svccode, price, dwages/price, dmatls
FROM xfile
WHERE ... ?

--
David Portas
SQL Server MVP
--

Oct 13 '05 #2
Thanks!! That did the trick!!

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
INSERT INTO Cost_Percent (svccode, price, dwages, dmatls)
SELECT svccode, price, dwages/price, dmatls
FROM xfile
WHERE ... ?

--
David Portas
SQL Server MVP
--

Oct 13 '05 #3
Ok I have another problem with some of the data. I did not notice this but
some of the columns where nvarchar data types and the math is not working on
them. I tried to change the data type to decimal but Enterprise Manager is
giving me and error saying it can not convert the data type.

The table I am working was imported and the data types were nvarchar but I
managed to change them to varchar data types but I need them to be numeric
or decimal data types so that the math will work.

Can any one tell how to change these?

Thanks!

"Mike" <no***@forgetit.com> wrote in message
news:5M***********@newssvr17.news.prodigy.com...
Thanks!! That did the trick!!

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
INSERT INTO Cost_Percent (svccode, price, dwages, dmatls)
SELECT svccode, price, dwages/price, dmatls
FROM xfile
WHERE ... ?

--
David Portas
SQL Server MVP
--


Oct 13 '05 #4
On Thu, 13 Oct 2005 15:50:35 GMT, Mike wrote:
Ok I have another problem with some of the data. I did not notice this but
some of the columns where nvarchar data types and the math is not working on
them. I tried to change the data type to decimal but Enterprise Manager is
giving me and error saying it can not convert the data type.

The table I am working was imported and the data types were nvarchar but I
managed to change them to varchar data types but I need them to be numeric
or decimal data types so that the math will work.

Can any one tell how to change these?


Hi Mike,

If you are 100% sure that all values in the data can be converted to
numeric datatype, you can use ALTER TABLE:

ALTER TABLE MyTable
ALTER COLUMN MyColumn new_datatype

If you do have bad data, you'll have to find and correct that first.
This query might help you find the dirty data:

SELECT something
FROM MyTable
WHERE REPLACE(REPLACE(MyColumn,'.',''),'-','') LIKE '%[^0-9]%'
OR MyColumn LIKE '%.%.%'
OR MyColumn LIKE '_-%'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Oct 13 '05 #5
Mike (no***@forgetit.com) writes:
Ok I have another problem with some of the data. I did not notice this
but some of the columns where nvarchar data types and the math is not
working on them. I tried to change the data type to decimal but
Enterprise Manager is giving me and error saying it can not convert the
data type.

The table I am working was imported and the data types were nvarchar but I
managed to change them to varchar data types but I need them to be numeric
or decimal data types so that the math will work.

Can any one tell how to change these?


One would guess that there is some junk data in that column that is
not convertible. (And which would explain the error you got when you
tried bulk insert.)

Try this for a start:

SELECT * FROM tbl WHERE isnueric(col) = 0

Review that data, and see if you can repair it.

Unfortunately, isnumeric() is not reliable, since it returns 1 if the
data can be converted to any numeric data type, so once you fixed those
with isnumeric = 0, you need to do refined checking. This is a possibility:

SELECT SUM(convert(decimal(28,14), col) FROM tbl

If this passes, you are fine. If it does not, you still have junk. If you
have a manageable number of service codes, you can do:

SELECT SUM(convert(decimal(28,14), col) FROM tbl WHERE SVCCODE = 'code1'
go
SELECT SUM(convert(decimal(28,14), col) FROM tbl WHERE SVCCODE = 'code2'
go

It's important that you run this in spearate batches, because the convert
error aborts the batch.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
1
by: Trent | last post by:
Hello,everyone. I'm setting a db2 replication environment using UDB version 8.1.5 running on Windows 2000 servers. The source server is on a Windows server with the capture program running while...
3
by: rrh | last post by:
I am trying to update a field in one table with data from another table. The problem I'm running into is I need to base the update on a range of data in the 2nd table. Table 1 has: date field...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
0
by: EKL | last post by:
Hi, I'm making a sort of Customer and Orders database in MS Access 2003. My problem is that I wish to update the table "tblTransaction" based on changes made in the table "tblOrderDetails"....
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
15
by: Darren | last post by:
Help, i want to run an update query from a form.. and was wonderin.. Can the update query run if i want to update a value manually inputted from a form (e.g. !!) to a table...
19
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without...
0
by: Access Programming only with macros, no code | last post by:
ERROR MESSAGE: Could not update; currently locked by another session on this machine. BACKGROUND I have the following objects: Table1 - HO (which has about 51,000+ records) Table2 -...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.