SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update Problem


Update Problem

Author
Message
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 420
hello

i want to update a data in grid view

i have three columns in grid view

first two column are shift id and date these two column are taken from table 1
the third column is shift column this column are taken from table 2

now i want to update a data where shift id and shift column are same

table 2 have these column
shift
shiftid

and table 1 has these column
shift id,
date

please can u provide me a update query syntax

thanks
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14315 Visits: 15975
We're not here to do your work for you, although obviously we're happy to help out if there's anything in particular you don't understand. Therefore please will you show us what you've tried so far? Also, you're more likely to get help if you post table DDL and sample data in the form of INSERT statements.

John
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 3670
Without proper DDL (create table) statements and sample data it is hard for us to understand and help you.

If I understand correct you have a view with data from 2 different tables. You want to alter the data in the view.
It is not possible to update two tables in a single statement. So you have to create two seperate update statement, one for each table. Specify the same value for "shift_id" in the WHERE clause of both update statements.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 420
update shift column with shift id

for example

shift id------------shift
---1-----------------A
---2-----------------B

after execute query data shows this result

shift id----------------shift
----1--------------------C
----2--------------------B
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 3670
update shift_table
set shift = case shift_id
when 1 then 'C'
when 2 then 'B'
else shift_id
end
where shift_id < 3



You can expand and/or alter the CASE statement to your specific needs. Also try to get a generic alteration of the new value. Like for example: you could alter the above sample to
update shift_table
set shift = char(ascii(shift)+1)
where shift_id < 3

if you need to take the next character for every value. Then a CASE statement is not required anymore.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 420
dont fix the values please
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 3670
immaduddinahmed (7/19/2013)
dont fix the values please
??
I don't understand what you mean. Please clearify.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 420
there are lots of shifts in shift table not only 1 and 2
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 3670
Do you need to change the values in a generic way (i.e.: add a fixed value, replace with a fixed value, etc.)?
Do you need to change the values in a generic way related to another column (i.e.: add value from column B to column A, depending on value in column B change value of column A to X else to Y, etc.)?
Do you need to change the values according to user/application input and these input is completely random?

If possible, provide some real world sample data and include the values before and after the update.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 420
I AM MAKING A APPLICATION IN c# THERE IS A GRID THAT SHOWS

SHIFT ID , SHIFT AND DATE

USER SEARCH DATE AND IN GRID DATE , SHIFT AND SHIFT ID SHOWS

THEN USER CHANGE SHIFT AND ON SHIFT TABLE THAT SHIFT NAME CHANGE OF THAT ID.

SHIFT ID AND DATE COME FROM SHIFT DETAILS TABLE

THIS IS A DATA

SHIFT ID----------DATE
-----1------------1/1/2013
-----2------------2/1/2013
-----3-----------4/5/2013
-----4------------6/24/2013

AND SHIFT COME FROM SHIFT TABLE

THIS IS A DATA OF SHIFT TABLE

SHIFT ID----------SHIFT
-----1------------A
-----2------------B
-----3-----------C
-----4------------D


WHEN USER EXECUTE UPDATE QUERY ITS CHANGES SHIFT COLUMN VALUE OF SHIFT TABLE

I HOPE U UNDER STAND SIR

ONE THING MORE HE CAN ONLY CHANGE A SHIFT FROM THE GRID HE CANNOT CHANGE ID
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search