March 28, 2011 at 9:19 am
hi all,
sorry for my poor english
hope someone can help me understanding something that i couldn´t in the pas few days...
some time ago i was told to perform a simple task!!! just grab some info from one column of a table and put it in another table´s column. simple right !!!!
So i open smss and typed this sql
update table_A set column_A =
(select columnB from tableB
where tableA.codigo = tableB.codigo)
smss try to execute and return this error:
Cannot insert the value NULL into column 'columnA', table 'database.dbo.tableA'; column does not allow nulls. UPDATE fails.
what i dont understand is why sql complains about null values if I dont have null values in the table!!!!
i have try this in a test environment where i do this task with only 3 values to make sure i really dont have a lost null somewhere.....
So why does sql complains about null values??
the target column is set to not allow null values but i´m not trying to put any null there.
givin up trying to do this in smss i tried to use Ms Access .
The sql instrution that is generated by Ms access is this:
UPDATE tableB AS tableB_1
INNER JOIN
tableA AS tableA_1 ON tableB_1.CODIGO = tableA_1.CODIGO, tableA
INNER JOIN
tableB ON tableA.CODIGO = tableB.CODIGO
SET tableA.CODEDI = [tableB]![CL01];
can someone explain this code? and why it works??
i just dont get it.
apreciate any help
March 28, 2011 at 9:26 am
In this:
update table_A set column_A =
(select columnB from tableB
where tableA.codigo = tableB.codigo)
If there isn't a matching value in tableB, based on the codigo column in tableA, then that will return a Null in the subquery, and that will violate the Not Null constraint.
Try changing it to a Select instead of an update, and you'll see what's going on.
If you're using SQL 2008, as per the forum you posted in, try using a Merge statement to get this done:
MERGE INTO table_A
USING tableB
ON table_A.codigo = tableB.codigo
WHEN MATCHED AND tableB.columnB IS NOT NULL THEN
UPDATE SET column_A = tableB.columnB
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 28, 2011 at 9:27 am
In you first example you are lookin at all records in TableA as such if a records does not exist in TableB then a NULL is returned. In the second Query the Join forces the Statemant to only look at records that exist in both TableA and TableB so no Null values are returned.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 28, 2011 at 9:29 am
this should work for you
update tableA
set column_A = column_B
from tableA
inner join tableB
on tableA.codigo = tableB.codigo
checkout this link for info about using update..from. there are some best practices and limitation specific to update..from
March 28, 2011 at 10:07 am
If you're using SQL 2008, as per the forum you posted in, try using a Merge statement to get this done:
MERGE INTO table_A
USING tableB
ON table_A.codigo = tableB.codigo
WHEN MATCHED AND tableB.columnB IS NOT NULL THEN
UPDATE SET column_A = tableB.columnB[/quote]
this did the trick.
thank you all for the explanation, I got it.
March 28, 2011 at 10:12 am
this should work for you
update tableA
set column_A = column_B
from tableA
inner join tableB
on tableA.codigo = tableB.codigo
and this do the trick also.
god i´m feeling so dumb right now:hehe:
thank you all.
March 29, 2011 at 6:09 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply