MS SQL Server - Update Query

Author

Update Query

Goldking

2006-10-28, 7:14 pm

Hi all,

I need ur help.

I have data like this.

Table name : mytable.

------------------------------------------------------------
sno Sem mark1 mark2
-----------------------------------------------------------
1 1 71 0
2 1 85 0
3 1 80 0
4 2 10 0
5 2 20 0
6 2 30 0
---------------------------------------------------------------

I want an update query to update all mark2 field have the same as mark1
field where SEM = 1

that is , The output after the updation is like :

------------------------------------------------------------
sno Sem mark1 mark2
-----------------------------------------------------------
1 1 71 71
2 1 85 85
3 1 80 80
4 2 10 0
5 2 20 0
6 2 30 0
---------------------------------------------------------------

Please give me the update Query.

Thanks in Advance.

with warm Regards,
Thangaraju

2006-10-28, 7:14 pm

try with:

Update mytable
set mark2 = mark1
from mytable
where mytable.SEM = 1

Its not ANSI syntax but it works on sql server and it should be pretty
obvious how it works...

MC

"Goldking" <gold.study@gmail.com> wrote in message
news:1162018081.367486.23820@b28g2000cwb.googlegroups.com...
> Hi all,
>
> I need ur help.
>
> I have data like this.
>
>
> Table name : mytable.
>
> ------------------------------------------------------------
> sno Sem mark1 mark2
> -----------------------------------------------------------
> 1 1 71 0
> 2 1 85 0
> 3 1 80 0
> 4 2 10 0
> 5 2 20 0
> 6 2 30 0
> ---------------------------------------------------------------
>
> I want an update query to update all mark2 field have the same as mark1
> field where SEM = 1
>
> that is , The output after the updation is like :
>
> ------------------------------------------------------------
> sno Sem mark1 mark2
> -----------------------------------------------------------
> 1 1 71 71
> 2 1 85 85
> 3 1 80 80
> 4 2 10 0
> 5 2 20 0
> 6 2 30 0
> ---------------------------------------------------------------
>
> Please give me the update Query.
>
>
> Thanks in Advance.
>
> with warm Regards,
> Thangaraju
>

David Portas

2006-10-28, 7:14 pm

MC wrote:
> try with:
>
> Update mytable
> set mark2 = mark1
> from mytable
> where mytable.SEM = 1
>
>
> Its not ANSI syntax ...
>

But it would be if you remove the FROM clause, which is completely
unnecessary in this case.

Goldking,
Please do not multi-post.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

2006-10-31, 12:15 am

I agree (offcourse :)), the point was to make it obvious how to expand the
update (some join possibly and similar)

MC

"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in message
news:1162019932.983071.281450@k70g2000cwa.googlegroups.com...
> MC wrote:
>
>
> But it would be if you remove the FROM clause, which is completely
> unnecessary in this case.
>
> Goldking,
> Please do not multi-post.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Sponsored Links