Update query help!!! - SQL Server Forums

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

Other Forums

MS Access

Update query help!!!

Author

Topic

gumubibi
Starting Member

6 Posts

Posted - 2009-09-05 : 05:32:29

Hi
I want to make an update query and I don't know how to make the query in this situation:
I have a table

Column
a1
a2
a3
c1
c2
c3

I want to transform c1 in a1, c2 in a2, c3 in a3 all this with one querry.

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-05 : 06:07:15

What is your expected result?


select c from 
(
	select 'a1' as  c union all
	select 'a2' union all 
	select 'a3' union all 
	select 'c1' union all 
	select 'c2' union all 
	select 'c3'
) as t order by LEFT(c,1) desc,RIGHT(c,LEN(c)-1)

Madhivanan

Failing to plan is Planning to fail

gumubibi
Starting Member

6 Posts

Posted - 2009-09-05 : 09:27:21

Hi

Thank you but I don't really think this will help. I'm sorry that i was not that clear in the matter. The problem is

Column (names)
ag. john
ag. mary
ag. roger
ca. john
ca. mary
ca. roger

The result I want to be is to transform ca. john as ag. john, ca. mary as ag. mary and ca. roger in ag. roger all in one update query.

The simple way is to make
Update table set table. column(names)="ag. "
Where (((table.column(names))="ca. "));

for each name.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-05 : 10:14:10

The simple way is to make
Update table set names=case when names like 'ca. ' then 'ag. '+substring(names,5,len(names)) else 'ca. '+substring(names,5,len(names)) end

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-05 : 12:51:31

update table
set names=replace(names,'ca. ','ag. '
where 'be_sure'+names like 'be_sureca. %'

No, you're never too old to Yak'n'Roll if you're too young to die.

gumubibi
Starting Member

6 Posts

Posted - 2009-09-05 : 14:07:52

Thank you all for your answers but nothing works.

Let put it in another way

Names

ag. mariane vanz
ag. roger vanz
ag. jhonny vanz
ca. mariane vanz
ca. roger vanz
ca. jhonny vanz

The logical thinking is like that: if names is "ca. mariane vanz" then transform "ca. mariane vanz" in ag. mariane vanz; same goes for the rest. And I want all this transformation in one statement, update query.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-05 : 14:10:23

So at the end there are two entries with ag. mariane vanz.
My query does that!

No, you're never too old to Yak'n'Roll if you're too young to die.

gumubibi
Starting Member

6 Posts

Posted - 2009-09-05 : 14:14:07

quote:
Originally posted by webfred

So at the end there are two entries with ag. mariane vanz.
My query does that!

No, you're never too old to Yak'n'Roll if you're too young to die.

??? I do not understand the be_sure thing. Can you help me a little bit with an example. And another thing. This must be done 3 times in our case with only one statement.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-05 : 14:17:27

[code]-- creating sample table
declare @yak table (names varchar(25))
-- insert sample data
insert @yak
select 'ag. mariane vanz' union all
select 'ag. roger vanz' union all
select 'ag. jhonny vanz' union all
select 'ca. mariane vanz' union all
select 'ca. roger vanz' union all
select 'ca. jhonny vanz'
-- diplay sample data
select * from @yak

-- output:
/*
names
-------------------------
ag. mariane vanz
ag. roger vanz
ag. jhonny vanz
ca. mariane vanz
ca. roger vanz
ca. jhonny vanz
*/

----------------
-- do the update
----------------
update @yak
set names=replace(names,'ca. ','ag. ')
where 'be_sure'+names like 'be_sureca. %'

-- display the new valaues
select * from @yak

-- output:
/*
names
-------------------------
ag. mariane vanz
ag. roger vanz
ag. jhonny vanz
ag. mariane vanz
ag. roger vanz
ag. jhonny vanz
*/[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-05 : 14:23:02

I have concatenated the string 'be_sure' only to prevent the replace command from replacing too much.
For example
'ac. yakidiyak' would not be a problem to replace 'ac. ' by 'ag. '
'ac. yaki ac. di ac. yak' would be a problem because we want only the first 'ac. ' to be replaced.
So my statement gives 'be_sureac. ' to the replace command for the string 'be_sureac. yaki ac. di ac. yak' and hence only the first value will be changed.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-05 : 14:24:28

Look at my sample there are 3 changes made in one go - is that ok?

No, you're never too old to Yak'n'Roll if you're too young to die.

gumubibi
Starting Member

6 Posts

Posted - 2009-09-05 : 14:26:52

quote:

Originally posted by webfred

-- creating sample table
declare @yak table (names varchar(25))
-- insert sample data
insert @yak
select 'ag. mariane vanz' union all
select 'ag. roger vanz' union all
select 'ag. jhonny vanz' union all
select 'ca. mariane vanz' union all
select 'ca. roger vanz' union all
select 'ca. jhonny vanz'
-- diplay sample data
select * from @yak

-- output:
/*
names
-------------------------
ag. mariane vanz
ag. roger vanz
ag. jhonny vanz
ca. mariane vanz
ca. roger vanz
ca. jhonny vanz
*/

----------------
-- do the update
----------------
update @yak
set names=replace(names,'ca. ','ag. ')
where 'be_sure'+names like 'be_sureca. %'

-- display the new valaues
select * from @yak

-- output:
/*
names
-------------------------
ag. mariane vanz
ag. roger vanz
ag. jhonny vanz
ag. mariane vanz
ag. roger vanz
ag. jhonny vanz
*/

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-05 : 14:28:06

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

gumubibi
Starting Member

6 Posts

Posted - 2009-09-05 : 15:02:17

Just to make the topic more interesting and complete for other interested forum members how about 2 separate definitions like:

Names
ag. jhonny vanz
agent mary vanz
ca. jhonny vanz
ca. mary vanz

The question is the same. Update query for the transformation of "ca. jhonny vanz" into "ag. jhonny vanz" and "ca. mary vanz" into "agent mary vanz" in one statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources