help needed updating one table with another

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

help needed updating one table with another

Author

Topic

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-16 : 12:17:22

Please help, I would like to update one table with another Where ID = ID

I get the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM webpages_backup WHERE webpages_backup.id = 2' at line 1

Here is my code:

UPDATE cactus_language.webpages SET id = webpages_backup.id,pagename = webpages_backup.pagename, pagetitle = webpages_backup.pagetitle, pagecontent = webpages_backup.pagecontent, keywords = webpages_backup.keywords, description = webpages_backup.description, consultant_id= webpages_backup.consultant_id, pagedomain = webpages_backup.pagedomain, updated = NOW(), active = webpages_backup.active, FROM webpages_backup WHERE webpages_backup.id = 2

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-19 : 03:59:33

can anyone help here please?

mhorseman
Starting Member

44 Posts

Posted - 2015-01-19 : 04:41:36

Looks like you need to remove an extra comma before the FROM

active = webpages_backup.active, FROM

Mark

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-19 : 05:53:21

thanks but I still get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM cactus_language.webpages_backup WHERE cactus_language.webpages.id = '2'' at line 1

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-19 : 06:09:49

don't I have to SELECT FROM first?

mhorseman
Starting Member

44 Posts

Posted - 2015-01-19 : 06:12:01

Looks like you've changed a few other things as well as removing the comma. I think the WHERE should say:

WHERE webpages_backup.id = 2

or possibly

WHERE cactus_language.webpages_backup.id = 2

depending upon what you're calling that table.

If that doesn't work, please repost your whole query.

Mark

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-19 : 06:55:59

here is my SQL:


UPDATE cactus_language.webpages SET
							id = cactus_language.webpages_backup.id,
						    pagename = cactus_language.webpages_backup.pagename,
							pagetitle = cactus_language.webpages_backup.pagetitle,
							pagecontent = cactus_language.webpages_backup.pagecontent,
							keywords = cactus_language.webpages_backup.keywords,
							description = cactus_language.webpages_backup.description,
							consultant_id= cactus_language.webpages_backup.consultant_id,
							pagedomain = cactus_language.webpages_backup.pagedomain,
							updated = NOW(),
						 	active = cactus_language.webpages_backup.active
					FROM cactus_language.webpages_backup 
					WHERE cactus_language.webpages_backup.id = '".$id."'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-19 : 07:38:45

There is no NOW function in SQL Server, Try GETDATE()

mhorseman
Starting Member

44 Posts

Posted - 2015-01-19 : 12:46:18

Whether you're doing this in MySQL or MS SQL Server, you might want to check what's happening in your UPDATE - at the moment you've got nothing to restrict which rows on the webpages table get updated from webpages_backup.

That might be what you want, if not then here's an example of what might do the trick (in MSSQL anyway):

create table #a (id int,info char(1))

insert into #a
select 1,'A' union
select 2,'B'

create table #b (id int,info char(1))
insert into #b
select 1,'L' union
select 2,'N'

update #a
set info = #b.info
from #a join #b on #a.id = #b.id

Mark

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-20 : 04:50:14

sorry Mark I don't understand yoru query, I already have table B

mhorseman
Starting Member

44 Posts

Posted - 2015-01-20 : 12:07:15

I was just trying to illustrate what you might need to do by creating and populating two tables #a and #b (equivalent to your webpages and webpages_backup tables), and then joining them. You've already got the tables, but you might still need a join, depending upon your requirements.

Mark

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-26 : 07:13:36

I just want to update one table with another

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-26 : 08:33:21

UPDATE cactus_language.webpages SET
id = cactus_language.webpages_backup.id,
pagename = cactus_language.webpages_backup.pagename,
pagetitle = cactus_language.webpages_backup.pagetitle,
pagecontent = cactus_language.webpages_backup.pagecontent,
keywords = cactus_language.webpages_backup.keywords,
description = cactus_language.webpages_backup.description,
consultant_id= cactus_language.webpages_backup.consultant_id,
pagedomain = cactus_language.webpages_backup.pagedomain,
updated = GetDate(),
active = cactus_language.webpages_backup.active
FROM cactus_language.webpages_backup
WHERE cactus_language.webpages_backup.id = '".$id."'

We are the creators of our own reality!

mhorseman
Starting Member

44 Posts

Posted - 2015-01-27 : 03:35:48

Note that sz1's solution should work fine if you want every record on webpages to be the same as '".$id."' from the webpages_backup table. If you want to update each record on webpages to the data from the same id on webpages_backup (if any), then you need something like:

UPDATE cactus_language.webpages SET
id = cactus_language.webpages_backup.id,
pagename = cactus_language.webpages_backup.pagename,
pagetitle = cactus_language.webpages_backup.pagetitle,
pagecontent = cactus_language.webpages_backup.pagecontent,
keywords = cactus_language.webpages_backup.keywords,
description = cactus_language.webpages_backup.description,
consultant_id= cactus_language.webpages_backup.consultant_id,
pagedomain = cactus_language.webpages_backup.pagedomain,
updated = GetDate(),
active = cactus_language.webpages_backup.active
FROM cactus_language.webpages join cactus_language.webpages_backup
on (whatever you want to join on)
WHERE cactus_language.webpages_backup.id = '".$id."'

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources