May 2, 2007 at 4:49 pm
Greetings.
I have never written a query in SQL, just worked with DTS packages. We are running SQL2000 that came with SBS2003.
I need to write what I think should be a very simple update query in SQL.
My source table (src_tbl) has 2 columns, lets say scol1 and scol2. My destination table (dest_tbl) has say 5 columns (dcol1, dcol2, etc…). dcol1 is a state code. dcol2 and scol1 are customer numbers and are the fields I will link the tables with. scol2 is a tax code. I need to update dcol3 with the values in scol2 IF dcol2 is equal to “WA”.
Additionally I need to put a “Y” in dcol5 if dcol1 is “WA”.
Is this a simple task? I figure it would take someone who was in the know 60 sec or less to write it.
Thanx
Mark
May 2, 2007 at 5:06 pm
UPDATE d
SET d.dcol3 = s.scol2
,d.dcol5 = 'Y'
FROM dest_tbl d
INNER JOIN src_tbl s
ON d.dcol2 = s.scol1
WHERE
d.dcol1 = 'WA'
Future reference, post you DDL (create table) scripts and most of the time, you'll get a solution that you can pretty much just paste in where you need it.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 2, 2007 at 9:06 pm
Sorry, I thought this was the NEWBIE forum.
May 2, 2007 at 9:13 pm
Mark,
It is a Newbie forum. That's why I Only gave one suggestion on future posts. You'll pick it up as time goes buy...
Sorry Joe, Not trying to slam you here, but this was Mark's FIRST post.....
Have a great day/night...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 2, 2007 at 11:26 pm
Joe,
I understand your point, the question could/should have been posted with more info, but it WAS enough information to give an answer to.
Again, there could have been more useful info provided, but how else does someone learn than by suggestions from their peers.
Mark did state that he's never worked with SQL other than DTS, so we shouldn't expect that he would know the "proper" way to post questions.
Give a guy a break! We were ALL newbies at one point.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 3, 2007 at 9:29 am
Jason, thanx for the inital post. It has me pointed in the right direction.
Now, if I knew what a DDL was I would post it.
The source database has not been created yet. I have a csv file that I will be using to create the source table. I will do this by using DTS.
As for the DDL for the destination database, I have no idea. It was created by our accounting application install (or the application tech created it before he installed the app). I will do a search and try to find out what a DDL is.
As for putting a "Y" (I will put double quotes if I like thank you) in a column, I DID NOT DESIGN the damn database. Our accounting software requires us to put a check in the "autotax 1" field if customers are going to be taxed. Checking this box simply puts a "Y" in a specific column for that customer.
Joe, you need to get out from behind the kyb more. Zero people skills man. Not everyone who comes here is a DBA. If I were I would not be seeking help. Oh, and by the way. I am not a programmer.
May 3, 2007 at 9:51 am
Mark,
DDL is "Data Definition Language" such as
CREATE TABLE [dbo].[AUDIT](
[AUDITID] [int] NOT NULL,
[PARTYID] [int] NOT NULL ………
To get the DDL for existing tables, right click on them in SQL Management Studio, select SCRIPT TABLE AS ... CREATE TO ....
This will give you the DDL, and it's one way to learn how to write it yourself.
Have a great day!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 3, 2007 at 9:55 am
I have enterprise manager so it was a little different. Is this correct (destination table only)?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientExtraInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ClientExtraInfo]
GO
CREATE TABLE [dbo].[ClientExtraInfo] (
[RecID] [int] IDENTITY (1, 1) NOT NULL ,
[CustLongCode] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContractExpDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FreightMethod] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustServiceRep] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AutoTax1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AutoTax2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductsCompany] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxCode1] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxCode2] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxCode3] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxCode4] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShippingFactor] [float] NULL ,
[ParentCompanyCode] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OverRunPercent] [float] NULL ,
[AdmnCode3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BrokeredNational] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Misc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NatAccountCode] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reserved] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LithoEstMarkUpType] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DefaultBillToShort] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DefaultBillToLong] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DefaultShipToShort] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DefaultShipToLong] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UploadDateStamp] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GLSalesAcct] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrencySymbol] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtensionNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ARAccount] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StdPONumber] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstValidDays] [int] NULL ,
[LongContact] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GLDetailFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[bkey1] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EasyViID] [int] NOT NULL ,
[StatementFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
May 3, 2007 at 12:09 pm
The application is being re-written in .net. The module that uses the customer info has not yet been re-designed. The application is not under inhouse development.
May 3, 2007 at 5:08 pm
Joe - to follow up on your earlier example - Hugo Kornelis, based on an example from Adam Mechanic...
I can understand why it doesn't work, but I'm wondering why EXISTS is needed in your proposed. I would have thought the following would have been sufficient:
UPDATE Orders -- no alias allowed!
SET some_col
= (SELECT item_price
FROM OrderDetails
WHERE OrderDetails.order_nbr = Orders.order_nbr)
I suppose I've taken this thread off it's original topic, but I wasn't sure where else to post this.
Thanks.
May 3, 2007 at 5:56 pm
Unfortunately, Joe is quite right.
If you are newbie in SQL Server you better start from reading some essential books about relational databases and managing data. Or refresh your memory if you've done it before. SQL is not just another programming language with another syntax. It requires another way of thinking, really.
When/if you've done it continue with separating entities in your dataset.
Any book will tell you that you need separate tables for different entities.
So, find and place in own tables Customer info, Contact, Contract, Trade Codes (Tax, GL, etc.), Trade Accounts, Transaction info, etc.
Pay attention to right datatypes. Datatype control is very important part of the model and helps prevent a lot of disasters.
Establish relations between those entities according to the nature of data and business rules applied to it.
Then you can move to creation of SP which will accept recordset from your csv file (I would recommend to use BULK INSERT, not DTS task) and distribute it over properly designed tables.
In my projects I use triggers on views.
Otherwise, yes, it's a right time to update your resume and try to go before they chuck you out.
P.S. The only Joe's statement I disagree is using FLOAT. I never noticed any significant performance hit because of float datatype.
The only point is not to use it where it's inappropriate.
Yes, you should not ever use it for amounts, because it's imprecise.
But it's absolutely appropriate (and recommended) for Factors.
_____________
Code for TallyGenerator
May 3, 2007 at 6:05 pm
1st of all it will fail if there are 2 or more rows with the same order_nbr in OrderDetails.
2nd, don't use varchar columns for joining tables.
3rd, it must be something like this:
UPDATE O -- alias is perfectly OK
SET some_col = MaxPrice
FROM Orders O
INNER JOIN (SELECT order_ID, MAX(item_price) MaxPrice -- or whatever you need to copy to header
FROM OrderDetails D
GROUP BY order_ID) ON D.order_ID = O.order_ID
_____________
Code for TallyGenerator
May 3, 2007 at 6:18 pm
Sergiy,
Isn't this backwards from what Mark is doing or am I looking at it wrong?
Using your example, He's updating the OrderDetails table from the Orders table. I understand this is making an assumption that there is only one entry in the orders table for each order. If that is in fact true, wouldn't my solution work?
If not, please explain, as I need to learn why too. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 3, 2007 at 6:19 pm
I do appreciate the help and everyones concern for my job. We employ 30 people, one of which is IT related. Me. We have no developers or programmers. I am a Server/Nework/Security guy and know nothing about programming and very little about databases. I am in charge of backing up the database and pulling reports out of it from time to time. I can not redesign the the database as our CRM/Manufacturing application is built upon the current structure. As I said it is being re-written but that is being done by the software vendor. I can not change anything about the database, nor am I expected to. We have a maitinance contract with the software company and if we have any issues they get to fix it.
The only reason I need to mess with the database is the tax codes for washington customers did not get pulled out of our old system. Therefore they did not get imported to the new system during the conversion from our old system (the aforementioned software company wrorte the import tool so I cannot modify it either). I figured inserting one value into a column based on a number in the key field (ok, I know that may not be what it is called, dont shoot me for not knowing the lingo please) and adding a Y (no quotes this time) to another field in the same row should be very easy.
And it is. Which is all I was after.
Thank you all for the help.
Mark
May 3, 2007 at 8:11 pm
If it's really the case, than I need to change my statement.
It's a right time to update your resume and try to go before they bankrupt or cancel IT project.
This kind of software works only if it's in development/toy stage.
As soon as somebody will get an idea to really use it this thing will fail.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply