SQL update a secondary table

I have a building file with certain info that I need to update in a listing file.
I can't figure out that the proper code.  Both files have a var called "SEQ".  This is the key.
Here's the code that I have:
UPDATE survey_data
SET
    survey_data.NOR = b1.NOR,
    survey_data.SOU = b1.SOU,
    survey_data.EAS = b1.EAS,
    survey_data.WES = b1.WES
FROM survey_data s1    
INNER JOIN bld1 b1 on s1.SEQ  = b1.SEQ
WHERE s1.SEQ = b1.SEQ;

Open in new window


Thanks
breeze351Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Try changing the first line from UPDATE survey_data to UPDATE sl.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
since you're using aliases, try:

UPDATE s1
SET
    s1.NOR = b1.NOR,
    s1.SOU = b1.SOU,
    s1.EAS = b1.EAS,
    s1.WES = b1.WES
FROM survey_data s1    
INNER JOIN bld1 b1 on s1.SEQ  = b1.SEQ
WHERE s1.SEQ = b1.SEQ;
breeze351Author Commented:
No joy!
I get the following error/message:

UPDATE s1
SET
    s1.NOR = b1.NOR,
    s1.SOU = b1.SOU,
    s1.EAS = b1.EAS,
    s1.WES = b1.WES
FROM survey_data s1    
INNER JOIN bld1 b1 on s1.SEQ  = b1.SEQ
WHERE s1.SEQ = b1.SEQ

MySQL said: Documentation
#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 survey_data s1    
INNER JOIN bld1 b1 on s1.SEQ  = b1.SEQ
WHERE s1.SEQ = ' at line 7

The code looks  good to me
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what about a simple one? >> don't use alias

UPDATE survey_data
SET
    survey_data.NOR = bld1.NOR,
    survey_data.SOU = bld1.SOU,
    survey_data.EAS = bld1.EAS,
    survey_data.WES = bld1.WES
FROM survey_data  
INNER JOIN bld1 on survey_data.SEQ  = bld1.SEQ

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
oops, that doesn't work in MySQL, try use this instead:

UPDATE 
survey_data  
INNER JOIN bld1 on survey_data.SEQ  = bld1.SEQ
SET
    survey_data.NOR = bld1.NOR,
    survey_data.SOU = bld1.SOU,
    survey_data.EAS = bld1.EAS,
    survey_data.WES = bld1.WES

Open in new window


or with aliases:
UPDATE 
survey_data s1
INNER JOIN bld1 b1 on s1.SEQ  = b1.SEQ
SET
    s1.NOR = b1.NOR,
    s1.SOU = b1.SOU,
    s1.EAS = b1.EAS,
    s1.WES = b1.WES

Open in new window

breeze351Author Commented:
AHHHH!!!!!

I can't get this.  
Here's the code in the php:
$String = "UPDATE survey_data  
SET
	survey_data.NOR = \"$north\",
	survey_data.SOU = \"$south\",
    survey_data.EAS = \"$east\",
    survey_data.WES = \"$west\"
INNER JOIN bld1 on survey_data.SEQ  = bld1.SEQ
WHERE bld1.SEQ = \"$Building_Id\"";
echo "str - ".$String."<br>";	

Open in new window


The "echo" string returns:
UPDATE survey_data SET survey_data.NOR = "34", survey_data.SOU = "33", survey_data.EAS = "H", survey_data.WES = "H" INNER JOIN bld1 on survey_data.SEQ = bld1.SEQ WHERE bld1.SEQ = "NY1050000350"

When I try to run the above string in PhpMyAdmin I get the following 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 'INNER JOIN bld1 on survey_data.SEQ = bld1.SEQ WHERE bld1.SEQ = "NY1050000350"' at line 1

Maybe I need more coffee or better yet a glass of wine!!!!!!!!!!!!!!!!
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
well in general, you got to try something like below:

UPDATE 
survey_data s1
INNER JOIN bld1 b1 on s1.SEQ  = b1.SEQ
SET
    s1.NOR = b1.NOR,
    s1.SOU = b1.SOU,
    s1.EAS = b1.EAS,
    s1.WES = b1.WES
where b1.SEQ = 'some value'

Open in new window


not too sure why you're setting survey_data's fields to variables instead of bld1's fields.

anyway, to apply correct syntax to your existing codes, you can try:

$String = "UPDATE survey_data  
INNER JOIN bld1 on survey_data.SEQ = bld1.SEQ
SET
	survey_data.NOR = '$north',
	survey_data.SOU = '$south',
    survey_data.EAS = '$east',
    survey_data.WES = '$west'
WHERE bld1.SEQ = '$Building_Id' ";

Open in new window

breeze351Author Commented:
I can't get this correct.
I added the code:
 
$String = "UPDATE 
survey_data s1
INNER JOIN bld1 b1 on s1.SEQ  = b1.SEQ
SET
    s1.NOR = b1.NOR,
    s1.SOU = b1.SOU,
    s1.EAS = b1.EAS,
    s1.WES = b1.WES
WHERE bld1.SEQ = '$Building_Id' ";

echo "str - ".$String."<br>";	

Open in new window


The echo returns:
UPDATE survey_data s1 INNER JOIN bld1 b1 on s1.SEQ = b1.SEQ SET s1.NOR = b1.NOR, s1.SOU = b1.SOU, s1.EAS = b1.EAS, s1.WES = b1.WES WHERE bld1.SEQ = 'NY1050000350'

But when I paste this into PHPMyadmin, it returns 0 records.

There is 1 record in bld1 and 4 in survey data.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
There is 1 record in bld1 and 4 in survey data.

so both tables come with record with SEQ = 'NY1050000350' ?

what suggested in ID: 42430779 was tested working. hence make sure that there are records that matched the conditions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
breeze351Author Commented:
Don't ask me.  I ran the same code and it worked!  Didn't change anything!!!!
Thanks
Glenn
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.