Update query question

Transact-SQL
1

Hello guys,

I have 2 tables, Bank and Confirm. I would like to update referenceId of Bank table with referenceId of Confirm. Here is the query;

select g.referenceId, gcr.referenceId, g.requestDateTime,gcr.requestDateTime
  from Confirm gcr
  inner join Bank g on g.requestDateTime=gcr.requestDateTime
  where g.referenceId = '00000000-0000-0000-0000-000000000001'

Here is the sample data:

2020-08-23 20_16_13-Kitap1 - Excel
2020-08-23 20_16_13-Kitap1 - Excel1070×194 8.6 KB

Can I update referenceId of Bank with an update statement?

2

hi

here is the update statement ..

-- run these statements first
+++++++++++++++++++++++++++++++
begin tran

UPDATE t1
SET t1.referenceId = t2.referenceId
FROM Bank t1
INNER JOIN Confirm t2 ON t1.requestDateTime = t2.requestDateTime
where t1.referenceId = '00000000-0000-0000-0000-000000000001'

selecy t1.referenceId,t2.referenceId
FROM Bank t1
INNER JOIN Confirm t2 ON t1.requestDateTime = t2.requestDateTime
where t1.referenceId = '00000000-0000-0000-0000-000000000001'
++++++++++++++++++++++++++++++++++++++++++

-- if results ARE Error then run this statement next
rollback tran

-- if results correct run this statement next
commit tran

3

Hi @harishgg1, thank you for your reply. Why do I need the select statement? I don't get it and so do i need begin trans, rollback trans and commit trans statements as well? I am going to run this on SQL Server Management Studio.

4

This is the way to do it :point_up:

5

decide :slight_smile: @yosiasz, which is the way?

6

The SELECT is there to visually ensure that it worked correctly because you provided the data as an image instead of readily consumable code and so he couldn't test it himself and is trying to safeguard your data and you.

You should get into the habit of providing readily consumable data in the form of a CREATE TABLE statement and an INSERT/VALUES statement. It will help avoid such a problem in the future and help others to help you better and more quickly.

I cannot speak for others but, typically, if someone doesn't care enough about their own problem to do so, then I don't care about it either and will just move on. There are lots of people than need help.

7

hi Cenk

the select statement is there to see if your update has worked ..

another version of the select statement is

selecy t1.referenceId,t2.referenceId
FROM Bank t1
INNER JOIN Confirm t2 ON t1.requestDateTime = t2.requestDateTime
where t1.referenceId = '00000000-0000-0000-0000-000000000001'
and t1.referenceId <> t2.referenceId

if this select statement .. returns 0 rows that means update worked
run the commit tran statement

if this select statement ..DOES NOT return 0 rows that means update did not work
run the rollback tran statement

8

thank you @harishgg1