March 11, 2009 at 10:07 am
I have a scenario that’s causing me some problems. I am getting there with SQL but I still have a lot to learn. I was wondering if there’s anyone that can offer some help/advise.
I have 2 tables as described below. Neither of these tables have primary keys. The ‘…’ represents a column that has no relevance to the query.
Table 1: Declaration_tax_claim_lines
Claim_number
…
…
batch_number
transaction_number
line_number
net_amount
amount_claimed
Table 2: Batch_transactions
Batch_number
Transaction_number
…
…
Transaction_date
…
Amount
…
…
…
…
…
…
…
…
…
…
…
…
…
Eligible_for_gift_aid
Based on a claim_number of ‘2’ from table 1 and a transaction_date of before 1 April 2003 from table 2 (joining on the batch_number and the transaction_number) I need to remove the row from table 1 and update the row in table 2 with eligible_for_gift_aid = ‘N’
I’ve written lots of select statements which give me the correct rows but it’s the delete and update parts that are tripping me up. I’ve tried dropping the relevant lines from table 2 into a temp table but again got lost as there’s not a single column to match on.
If anyone can work out what I'm trying to do and come up with some suggestions, it would be greatly appreciated.
March 11, 2009 at 10:21 am
if you can provide the actual CREATE TABLE statements, and a few rows of sample data, we could really help a lot better.
off hand, i think you'll update table2 before you do anything.
something like
UPDATE batch_transactions TABLE2
SET
eligible_for_gift_aid = 'N'
FROM Declaration_tax_claim_lines TABLE1
WHERE TABLE1.batch_number = TABLE2.batch_number
and TABLE1.transaction_number = TABLE2.transaction_number
and TABLE1.claim_number = '2'
and a TABLE1.transaction_date < '04/01/2003'
then the second step would be a simple delete?
DELETE FROM Declaration_tax_claim_lines TABLE1
WHERE TABLE1.claim_number = '2'
and a TABLE1.transaction_date < '04/01/2003'
Lowell
March 11, 2009 at 10:24 am
Rats, Lowell beat me to it... 🙂
You probably want a transaction here too.
And long term, you seriously want primary keys.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2009 at 2:16 am
Thanks for replying guys.
I can't get the update code to parse 'Incorrect syntax near 'TABLE2' (there's also not a transaction_date in table 1 but I presume that the join makes that easy enough to just change to table2 for this code but the delete code could still be a problem?
I was basically on the same lines as this before I started having problems. I was wondering whether I should I use a cursor here instead but don't really know if that's appropriate or indeed how that would work.
As for the primary keys, I completely agree. It's a 3rd party app that I can't change but my jaw hit the floor when they told me they weren't using them as I knew only too well that these were exactly the sort of problems I would face.
Many thanks,
Peter
March 12, 2009 at 3:09 am
Create table code as requested...
/****** Object: Table [dbo].[declaration_tax_claim_lines] Script Date: 03/12/2009 09:07:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[declaration_tax_claim_lines](
[claim_number] [int] NOT NULL,
[cd_number] [int] NOT NULL,
[declaration_or_covenant_number] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[batch_number] [int] NOT NULL,
[transaction_number] [smallint] NOT NULL,
[line_number] [smallint] NOT NULL,
[net_amount] [decimal](9, 2) NOT NULL,
[amount_claimed] [decimal](9, 2) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[batch_transactions] Script Date: 03/12/2009 09:08:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[batch_transactions](
[batch_number] [int] NOT NULL,
[transaction_number] [smallint] NOT NULL,
[contact_number] [int] NOT NULL,
[address_number] [int] NOT NULL,
[transaction_date] [datetime] NOT NULL,
[transaction_type] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[bank_details_number] [int] NULL,
[amount] [decimal](9, 2) NOT NULL,
[currency_amount] [decimal](13, 2) NULL,
[payment_method] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[reference] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[next_line_number] [smallint] NULL,
[line_total] [decimal](9, 2) NOT NULL,
[mailing] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[receipt] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mailing_contact_number] [int] NULL,
[mailing_address_number] [int] NULL,
[amended_by] [varchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[amended_on] [datetime] NOT NULL,
[eligible_for_gift_aid] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
I'll attach some sample data soon.
Peter
March 12, 2009 at 7:15 am
If you are using Lowell's query then just change TABLE1 to "declaration_tax_claim_lines" and TABLE2 to batch_transactions, and take out the extra reference on the first line.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2009 at 7:33 am
That seems to have worked on my test system, I will be trying to run it later on live when I have no users getting in my way.
Just out of interest, can you not alias tables when doing an update or delete statement then?
Thanks for your help, you guys are great.
Peter
March 12, 2009 at 8:00 am
You can, but the syntax is unusual (and undocumented, I believe):
UPDATE AliasName
SET ...
FROM RealName
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2009 at 2:27 am
Nearly there.
I now need to turn this select statement into a delete but am not having much luck.
select * from declaration_tax_claim_lines
inner join batch_transactions
on declaration_tax_claim_lines.batch_number = batch_transactions.batch_number
and declaration_tax_claim_lines.transaction_number = batch_transactions.transaction_number
where batch_transactions.transaction_date > '2004-04-01'
and declaration_tax_claim_lines.claim_number = 2
Any ideas?
Many thanks,
Peter
March 13, 2009 at 2:54 am
Have you tried this one?
delete declaration_tax_claim_lines
from batch_transactions
where
declaration_tax_claim_lines.batch_number = batch_transactions.batch_number
and batch_transactions.transaction_date > '2004-04-01'
and declaration_tax_claim_lines.claim_number = 2
March 13, 2009 at 2:55 am
And please, add primary keys to your tables, or at least, a few indexes.
I didn't see indexes in your DDL.
March 13, 2009 at 5:44 am
serinor.e090266 (3/13/2009)
And please, add primary keys to your tables, or at least, a few indexes.I didn't see indexes in your DDL.
That's already been explained by the OP as the dreaded 3PV problem (Third Party Vendor).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply