[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Use a select statement to update another table

Posted on 2011-03-22
2
Medium Priority
?
267 Views
Last Modified: 2012-08-14
I would like the check_number from the payments table to be updated from the invoice table.

Thanks
SELECT TOP 1  i.invoice_id, i.check_number,ip.ipid, p.check_number
FROM invoice i, invoice_payments ip, payments p
WHERE i.invoice_id = ip.inv_id
AND p.p_id = ip.p_id
AND i.check_number <> 0

UPDATE payments
SET check_number = i.check_number
WHERE p_id = p.p_id

Open in new window

0
Comment
Question by:aka_FATCAT
2 Comments
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 35195442
try this
UPDATE p
SET check_number = i.check_number
FROM invoice i, invoice_payments ip, payments p
WHERE i.invoice_id = ip.inv_id
AND p.p_id = ip.p_id
AND i.check_number <> 0

Open in new window


note: take backup of your original data or run it in a transaction and after checking the data commit the transaction.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35195444
try this
declare @CheckNumber integer, @id integer


SELECT TOP 1 @CheckNumber = i.check_number, @id = p.p_id
FROM invoice i, invoice_payments ip, payments p
WHERE i.invoice_id = ip.inv_id
AND p.p_id = ip.p_id
AND i.check_number <> 0

if (@CheckNumber is not null) and (@CheckNumber > 0)
begin
	UPDATE payments
	SET check_number = @CheckNumber
	WHERE p_id = @id
end

Open in new window

0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question