copying to rows - 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

copying to rows

Author

Topic

mallikohomada
Starting Member

5 Posts

Posted - 2008-12-08 : 09:15:31

Hi all

I need to find out how to apply an empty string to a file.

I have tow queries,

1 )

SELECT FileList.sFilename
INTO holdkey
FROM FileList
GROUP BY sFilename
HAVING count(*) > 1

This check for any duplicate line then create a new table “holdkey”
And past it there.

Now I want to compare the the original table with the duplicate table “holdkey”
And copy an empty string in to those duplicate rows

UPDATE FileList
SET FileList.sFilename= ‘’
WHERE FileList.sFilename = holdkey.sFilename

My issue is, every time I run this, it ask for set pareameter
If is do set @sFilename=’’
It displays 0 rowsa updated

Any help on this please
ps I ma new to sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 09:22:06

[code]UPDATE f
SET f.sFilename= ''
FROM FileList f
JOIN holdkey h
ON f.sFilename = h.sFilename
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-08 : 09:38:17

quote:

Originally posted by visakh16

UPDATE f
SET f.sFilename= ''
FROM FileList f
JOIN holdkey h
ON f.sFilename = h.sFilename

In ACCESS it should be

UPDATE f
FROM FileList f
JOIN holdkey h
ON f.sFilename = h.sFilename
SET f.sFilename= ''

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 09:43:33

ah...missed that its Access forum...

mallikohomada
Starting Member

5 Posts

Posted - 2008-12-08 : 10:07:02

guys thanks for the reply
I still get
syntax error UPDATE statment

UPDATE filelist
FROM FileList
JOIN holdkey
ON filelis.sFilename = holdkey.sFilename
SET filelist.sFilename= ''

also if you dont mind explaining it(only if you wish)
thx

mallikohomada
Starting Member

5 Posts

Posted - 2008-12-09 : 06:09:22

Please can some one help me on this.
thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-10 : 07:27:09

UPDATE f
FROM FileList f
JOIN holdkey h
ON f.sFilename = h.sFilename
SET f.sFilename= ''

Madhivanan

Failing to plan is Planning to fail

mallikohomada
Starting Member

5 Posts

Posted - 2008-12-17 : 06:17:39

sorry, I still get the "syntax error in UPDATE statment"

nheidorn
Starting Member

28 Posts

Posted - 2008-12-18 : 14:08:44

Try this:

UPDATE FileList INNER JOIN holdkey ON FileList.sFileName = holdkey.sFileName
SET FileList.sFileName = '';

Access is pretty picky about the syntax of UPDATE statements.

mallikohomada
Starting Member

5 Posts

Posted - 2008-12-19 : 07:02:28

u r the man, yes it worked
thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources