UPDATE SQL not working

Question

Post reply

UPDATE SQL not working

BigB

SSC Eights!

Points: 828
More actions
July 27, 2016 at 11:46 pm

#309857

Hi,
I am trying to update one table's column values with another table's column value but update is not updating any rows. I have created 2 tables tab1(id) where id is null in all records and tab2(val) with 1,2,3 as values in 3 records. I am getting result of select query but not able to understand why update is not updating id column with val column?
update t1
set t1.id=t2.val
from tab1 t1
right outer join tab2 t2 on t1.id=t2.val
select *
from tab1 t1
right outer join tab2 t2 on t1.id=t2.val
******output of select query*******
idval
NULL1
NULL3
NULL4
******message for update SQL******
(0 row(s) affected)
******message for select SQL******
(3 row(s) affected)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the output of:

SELECT * FROM tab1

SELECT * FROM tab2

?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

BigB SSC Eights! Points: 828 More actions · Answer 2

tab1 contains only one column which is null in all records and tab2 also contains one column which is not null and having data. I want to update tab1.id with tab2.id.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

bhushanbagul (7/28/2016)
tab1 contains only one column which is null in all records and tab2 also contains one column which is not null and having data. I want to update tab1.id with tab2.id.

How many rows in each table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

BigB SSC Eights! Points: 828 More actions · Answer 4

Hi,

There 3 rows in tab1 where ID is null and 3 rows in tab2 where ID's are 1,3,4.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

The answer to your question is "there are no qualifying rows in t1 to update". You're mistaking NULL as a placeholder for NULL as a value.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nilanaksha SSC Eights! Points: 911 More actions · Answer 6

You insert a single NULL row into Tab1 instead of three and still you get the same output from the select query. All the NULL values from Tab1 with the right join means it does not have any corresponding matching rows in Tab2 and so no update. Do not get confuse. Check the definition of OUTER JOIN.

BigB SSC Eights! Points: 828 More actions · Answer 7

Hi

Yeah I know how outer join works. Just wondering why it is not updating tab1.id when select returning the result set. Anyway, how can this be achieved then?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

BigB (7/28/2016)
Hi
Yeah I know how outer join works. Just wondering why it is not updating tab1.id when select returning the result set. Anyway, how can this be achieved then?

The select does not return any rows from t1.

Can you set up ddl and dml for the two tables (a script to create and populate the two tables)? There are one or two simple ways to do this update.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nilanaksha SSC Eights! Points: 911 More actions · Answer 9

For the update to happen you need to define keys on both the tables. Also the table Tab1 with three same NULL rows does not even pass NF1.

Sergiy SSC Guru Points: 110208 More actions · Answer 10

BigB (7/28/2016)
Hi
Yeah I know how outer join works. Just wondering why it is not updating tab1.id when select returning the result set.

Which particular value from t2 you're assigning to each of identical NULL rows in t1?

Until you can answer this question your update does not have any chance to be working.

Anyway, how can this be achieved then?

delete tab1

where id is null

insert t1 (id)

select t2.val

from tab2 t2

where not exists (

select *

from tab1 t1

where t1.id=t2.val

)

_____________
Code for TallyGenerator

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

BigB (7/28/2016)
Just wondering why it is not updating tab1.id when select returning the result set. Anyway, how can this be achieved then?

Because there's no matching rows in tab1, so your query is essentially

select NULL as id, *

from tab2 t2

If you go and manually update the IDs in tab1 to something like -1 and run the select again, you'll still see NULLs for the first column, because no rows match from t1 so the output column from it is always going to be null, and the update will affect 0 rows, as there are no valid rows in t1 from that join

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass