[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Use a select statement to update another table

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
aka_FATCAT
Asked:
aka_FATCAT
1 Solution
 
appariCommented:
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
 
Ephraim WangoyaCommented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now