please help me with sql server update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

please help me with sql server update

Author

Topic

KarinElvira
Starting Member

47 Posts

Posted - 2011-01-25 : 04:20:55

I'm trying to update rows in my database table but it doesn't work. I want to
update row 'status'
where status = 'obokad' in table 'Meetings'
and where the date registered in row 'dateStart' in table 'Occasion' is equal to a date chosen by the user -'datevalue'
and where 'Occasion.meetingID' is equal to 'Meetings.ID'.

At the moment I've gotten to two alternatives:


string strQuery = "UPDATE Meetings SET status = 'ogiltig' FROM Meetings JOIN Occasion ON Meetings.ID = Occasion.meetingID"
        + " WHERE Meetings.status = 'obokad' AND CONVERT(VARCHAR(50), Occasion.dateStart, 101)"
        + " <= CONVERT(VARCHAR(50),convert(datetime, @datevalue), 101)";

and


string strQuery = "UPDATE Meetings SET status = 'ogiltig' FROM Meetings JOIN Occasion ON Meetings.ID = Occasion.meetingID"
        + " WHERE Meetings.status = 'obokad' AND datediff(dd,Occasion.dateStart,getdate())<=0";

What do I need to do to make it work? Thankful for help!

[dbo].[Meetings](
[ID] [int] IDENTITY(1,1) NOT NULL,
[status] [varchar](50) NOT NULL,
[subject] [varchar](50) NOT NULL,
[description] [varchar](max) NOT NULL,
[memberID] [int] NOT NULL,

[dbo].[Occasion](
[ID] [int] IDENTITY(1,1) NOT NULL,
[dateStart] [smalldatetime] NOT NULL,
[dateStop] [smalldatetime] NOT NULL,
[meetingID] [int] NOT NULL,

string datevalue = DateTime.Today.ToString("yyyy/MM/dd");
cmd.Parameters.AddWithValue("@datevalue", datevalue);

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-25 : 04:37:48

It would be helpful if you explain the "Not Working". are you getting any error or..... ?

KarinElvira
Starting Member

47 Posts

Posted - 2011-01-25 : 04:49:32

Sorry. No error, my problem is that nothing happens.

KarinElvira
Starting Member

47 Posts

Posted - 2011-01-25 : 05:24:03

How can I check if an update statement uses any values? I think that would help, to start with.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-25 : 05:53:01

Perhaps Either you are running the above statements without executing it? e.g. is there any statement e.g.

Exec (@string)

in your code .. if its there and still you get a message as "0 Rows Effected" under the messages tab of SSMS, Then it might be that there is no row as per your From, joins and Where clause..

execute the following query and let us know if it returns any/how many row(s)?

Select Meetings.ID, Occasion.meetingID
FROM Meetings JOIN Occasion ON Meetings.ID = Occasion.meetingID
WHERE Meetings.status = 'obokad'
And Occasion.dateStart between 'YYYY-MM-DD' and 'YYYY-MM-DD'

Replace the red section with desired Dates and let me know if it returns any information?

KarinElvira
Starting Member

47 Posts

Posted - 2011-01-25 : 06:29:06

I know the rows exist, I've added them myself to the database and I can se them when I run the program. My problem is that I want to update the status of certain rows

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-25 : 06:58:43

quote:
Originally posted by KarinElvira

I know the rows exist, I've added them myself to the database and I can se them when I run the program. My problem is that I want to update the status of certain rows

I did not deny that you have not inserted any row into the tables. I just said that check as if the rows are selecting as per given condition. therefore

1) the status of certain rows will only be updated only if a Select query as per "specified (in the update statement ) joins and where conditions" returns any result set.

2) if it does then the Following update should work for same number of rows!

Update Meetings

Set Status='ogiltig'

FROM Meetings JOIN Occasion ON Meetings.ID = Occasion.meetingID
WHERE Meetings.status = 'obokad'
And Occasion.dateStart between 'YYYY-MM-DD' and 'YYYY-MM-DD'

You should see "xxx rows Updated" message in the messages tab of your SSMS

KarinElvira
Starting Member

47 Posts

Posted - 2011-01-25 : 07:32:46

My first alternative in my first post is now working (whithout any changes), I'm not sure what went wrong before. Thank you for your time, and I will come back to your suggestions if I have a similar problem another time!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources