breeze351
asked on
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:
Thanks
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;
Thanks
Try changing the first line from UPDATE survey_data to UPDATE sl.
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;
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;
ASKER
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
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
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
oops, that doesn't work in MySQL, try use this instead:
or with aliases:
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
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
ASKER
AHHHH!!!!!
I can't get this.
Here's the code in the php:
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!!!!!!!!!!!!!!!!
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>";
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!!!!!!!!!!!!!!!!
well in general, you got to try something like below:
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:
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'
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' ";
ASKER
I can't get this correct.
I added the code:
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.
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>";
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Don't ask me. I ran the same code and it worked! Didn't change anything!!!!
Thanks
Glenn
Thanks
Glenn