|
Look at the error message you receive:
ERROR: null value in column "dt_open_date" violates not-null constraint
DETAIL: Failing row contains (VC2012030000303, VC20100300266, null, 11, 5, RBS, 1, 40, 40, 0, 14, 0, 1, 0, 1, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0).
It clearly explains, and shows, that you have a record with a null value where one is not allowed. You need to use your database management tools to find that record and fix its content.
|
|
|
|
|
ya i have checked it but ...there is no null vaue ....i just send u screen shot of my tables then hope u understand my issue ok.
|
|
|
|
|
Member 11919722 wrote: hope u understand my issue
Yes, I do understand your issue, but I do not think you do.
Either:
- You have a record in your database that contains a null value, or
- The
select dt_bill_date part of your update query does not find a record, so you are passing in a null value.
So, the solution is to find out why the select part of your update is returning a null value, or why that record contains null in the database.
|
|
|
|
|
thats wt how ,, i searched alot and tried solving but there is no null value though its giving
|
|
|
|
|
Run the select part of your query and see what value gets returned.
|
|
|
|
|
its returning the dates greater than 2015-07-01 without any null rrecords for dt_bill_date column of sc_bill table but i want same record to be displayed for dt_open_date column of sc_bill_detail ...thats what my requirement actually
|
|
|
|
|
I suggest you try modifying your code so it iterates through all the results doing the update for each found record.
|
|
|
|
|
i am not writting any code its only a query which i need to write according to the requirement as i told u btw my approach of writting query is correct ?or any other way to get it...coz i think its the simplest one?
modified 23-Sep-15 3:45am.
|
|
|
|
|
Member 11919722 wrote: i think its the simplest one
Yes, but it does not work, so it is not the correct one.
|
|
|
|
|
it did work coz i checked the same query with another two tables which i created as an example...n run same query on those two tables ...and getting desired output as well...so its correct one i think
|
|
|
|
|
So if the query works on different tables with the same structure, then you've proved that the problem is with the data in the tables you're using when it doesn't work.
Since we don't have access to your database, we can't diagnose the problem for you. You'll need to check the data in the problem tables to see why the inner query isn't returning a result.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thanks.... finally i achieved my requirement.
|
|
|
|
|
can u give me ur mail id so that i can send u my tables structure ....
|
|
|
|
|
let me tell you the rwquirement of my query that i have wriiten ...so that u could tell me whether my query is right or wrong according to the requirement..
actually i have two tables sc_bill and sc_bill_details...and i have requirement to get the dates available in sc_bill table ( from july 2015 to till date) to sc_bill_details...is my approch is correct to get the desired output.? i want only query
Thanks
|
|
|
|
|
Read the entire thread to date.
If you run JUST the select:
Member 11919722 wrote: select dt_bill_date from sc_bill where sc_bill.st_bill_no=sc_bill_details.st_bill_no and dt_bill_date>='1-7-2015'
what values do you get? Do you get a record with a NULL date?
|
|
|
|
|
You'll need an inner join to the sc_bill_details table to run the query without the outer UPDATE statement.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Correct... missed that; thanks for catching it.
So... reword the update to be a query with an inner join.
|
|
|
|
|
the thing is that if m writing same query with two defferent tables but not with sc_bill and sc_bill_details....m getting the required output but not with tables that i should get.
|
|
|
|
|
i did as u said n got output also without null values but if m writing the same query...with update statement as outer query the inner query gives foloowing error which is obvious coz inner query returns more than single value ..the query will be as follow as u said right?
update sc_bill_details set dt_open_date=(select dt_bill_date from sc_bill,sc_bill_details where sc_bill.st_bill_no=sc_bill_details.st_bill_no and dt_bill_date>'2015-07-01' )
getting follwing error when wrote above query ....
more than one row returned by a subquery used as an expression
i cant even use in operator with update statement since that will lead to syntax error??
|
|
|
|
|
m not getting any null value if m writing inner query ...
|
|
|
|
|
Hi All,
I want to migrate database from Mysql Server to Sql Server.I migrated database tables structure and Stored Procedures successfully and some database records also by using Sql Server Migration Tool for Mysql.But all the table records not move.
So what I do please give me guidance for this.
|
|
|
|
|
ALTER PROCEDURE [dbo].[SP_SearchByUserNeed]
(
@ColumnName varchar(20),
@Value Varchar(100)
)
AS
BEGIN
BEGIN try
Select * From Product where @ColumnName like @Value+'%';
END try
BEGIN catch
print('Error in [SP_SearchByProductName]')
END catch
END
|
|
|
|
|
You cannot use a variable in this manner. You would need to use dynamic code.
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Hay, Thank you so much for your valuable response. I am agree with you. But i can't find a way to do the replacement of the column name dynamically with a variable. If possible please explain the same. I believe. Thank you
|
|
|
|
|
Try something like this - as I am on holiday I have not had a chance to test this:
ALTER PROCEDURE [dbo].[SP_SearchByUserNeed]
(
@ColumnName varchar(20),
@Value Varchar(100)
)
AS
BEGIN
BEGIN try
declare @qry nvarchar(max);
set @qry = 'Select * From Product where @ColumnName like ' + '''' + @Value + '%' + '''';
exec(@qry);
END try
BEGIN catch
print('Error in [SP_SearchByProductName]')
END catch
END
Hugs and kisses to the downvoter ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 16-Sep-15 8:55am.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
