sql server programming: I have 2 existing tables containing which I need reorganize
First table CheckLog contains records representing Company Checks sent to
Suppliers. The second table Payments contains records representing Payments
for Particular orders. This tables related by CheckID (primary key in Check
table and Foreign key in Payment table). The bad thing is: Instead if 1
record in Check table, representing the particular check and several related
records in the Payment table, I have the same number of records in the Check
table as the number of Payments from that check. For instance I have in
Payment table these records:

PaymentID CheckID Amount
1 1 $100
2 2 $150
3 3 $250

In Check table instead of having 1 single record for the Check:
CheckID CheckAmount CheckDate CheckNumber SupplierID
1 $500 10/01/2003 123
1

I have 3 records for the same check:
CheckID CheckAmount CheckDate CheckNumber SupplierID
1 $500 10/01/2003 123
1
2 $500 10/01/2003 123
1
3 $500 10/01/2003 123
1

I would like to update records in Payment Table to these values:
PaymentID CheckID Amount
1 1 $100
2 1 $150
3 1 $250

Then delete 2 duplicates from Check table in order to have just a singlr
record for the check:
CheckID CheckAmount CheckDate CheckNumber SupplierID
1 $500 10/01/2003 123
1

Is there any way to do it with 1 or several SQL statements or I need to
write some program in VB (for instance) to go through all records in both
tables, make some analyses and then take some actions?

Thank you
Vlad

David,
Thank you very much
Vlad

[quoted text, click to view]

David,
Unfortunately I have not enough experience to find out what's wrong with
your SQL, but it doesn't work. I tested the first (Update) statement only.
It says "Syntax Error"
Vlad

[quoted text, click to view]

The following UPDATE/DELETE should do it:

UPDATE Payments
SET checkid =
(SELECT MIN(C1.checkid)
FROM Checks AS C1
JOIN Checks AS C2
ON C1.checknumber = C2.checknumber
AND C2.checkid = Payments.checkid)

Then delete the duplicate checks:

DELETE FROM Checks
WHERE EXISTS
(SELECT *
FROM Checks AS C
WHERE checknumber = checks.checknumber
AND checkid < Checks.checkid)

Make sure this can't happen again by making CheckNumber the PK of the Checks
table and using it as the FK in Payments. Drop the redundant Checkid column.

UPDATE Payments
SET checknumber =
(SELECT checknumber
FROM Checks
WHERE checkid = Payments.checkid)

--
David Portas
------------
Please reply only to the newsgroup
--

David, thank you
I'm trying to do this stuff in Access database. Before if I needed some help
with SQL statements I used to post my questions on Access news group, but
usually no answer there. Then because SQL is similar to both SQL server and
Access, I started to post my questions here, in SQL Server news group.
Usually I get answers and always they work. For this case unfortunately
doesn't.
So I cannot post the script for table creation (I don't know how to create
it in Access from existing table).
Anyway, thank a lot.
I'm working now on my task through VB. For me it's easier to write several
simple single statements than to look for a problem in your statement
Vlad

[quoted text, click to view]

It works for me. Here's the script with some test DDL to prove it can work.
If you want more help you'll have to post your DDL (CREATE TABLE statements
for the tables - simplified where possible but including the relevant
columns, keys and constraints).

What version of SQLServer are you using?

CREATE TABLE Payments (paymentid INTEGER PRIMARY KEY, checkid INTEGER NOT
NULL, checknumber INTEGER NOT NULL)
CREATE TABLE Checks (checkid INTEGER PRIMARY KEY, checknumber INTEGER NOT
NULL)

UPDATE Payments
SET checkid =
(SELECT MIN(C1.checkid)
FROM Checks AS C1
JOIN Checks AS C2
ON C1.checknumber = C2.checknumber
AND C2.checkid = Payments.checkid)

DELETE FROM Checks
WHERE EXISTS
(SELECT *
FROM Checks AS C
WHERE checknumber = checks.checknumber
AND checkid < Checks.checkid)

UPDATE Payments
SET checknumber =
(SELECT checknumber
FROM Checks
WHERE checkid = Payments.checkid)

--
David Portas
------------
Please reply only to the newsgroup
--

In your original post, you mentioned VB. First, test out the statements I
posted using Query Analyser. You can put it into your VB code later if you
need to. Make sure you have an up-to-date backup before you attempt
anything.

--
David Portas
------------
Please reply only to the newsgroup
--

Access's UPDATE syntax is different to standard SQL. You'll have to post to
an Access group as I'm not an expert.

For future posts it might help to say from the start that you're not using
SQLServer!

--
David Portas
------------
Please reply only to the newsgroup
--